Answer simple quiz at the end of the blog post and –
Every day one winner from India will get Joes 2 Pros Volume 5.
Every day one winner from United States will get Joes 2 Pros Volume 5.
What is XML?
A common observation by people seeing an XML file for the first time is that it looks like just a bunch of data inside a text file. XML files are text-based documents, which makes them easy to read. All of the data is literally spelled out in the document and relies on a just a few characters (<, >, =) to convey relationships and structure of the data. XML files can be used by any commonly available text editor, like Notepad.
Much like a book’s Table of Contents, your first glance at well-formed XML will tell you the subject matter of the data and its general structure. Hints appearing within the data help you to quickly identify the main theme (similar to book’s subject), its headers (similar to chapter titles or sections of a book), data elements (similar to a book’s characters or chief topics), and so forth. We’ll learn to recognize and use the structural “hints,” which are XML’s markup components (e.g., XML tags, root elements).
Applications and websites scan XML and read these components to understand the organization and structure of the data. Additionally, XML can include instructions for how a program should consume its data. To understand how this works, we first must discuss the concept of data and metadata.
Metadata vs. Metadata
In short, metadata is data about data. A timestamp, a row number, a column name, the name of table, a data type – these are examples of metadata, because they help characterize and describe what kind of data you have but aren’t actually part of the raw data itself. Metadata describes your data, including relationships within your data.
When we were in grade school we learned how indentation is a simple formatting cue used to show structure, such as subtopics belonging to a topic. In the right hand side of the figure below the Data List B indentation serves as metadata because it helps us understand the relationships between the items in the list. Later we will see the layout of an XML document includes fairly simple cues, like indentation and tags to help distinguish one hierarchical level from another.
Since the heading “Veggies” appears below the title “Food” and is indented once, we know Veggies is a category belonging to Food. “Meat” and “Fruit” appear at the same level as Veggies, so they are a peer of Veggies and also a type of Food. Below each type sits a further indented level, which indicates that these items (Carrots, Chicken, Banana, etc.) are types of Veggies, Meat, and Fruit and are sub-types of Food.
Much like the examples above, XML is comprised of two components:
1) Data – The information you have.
2) Metadata – the description of the data, including relationships and properties of the data.
Streams
In the IT world, the term stream signifies the output of information by a program or process. Don’t feel badly if that definition doesn’t immediately register with you. It took me some time to understand the “streams” concept. A colleague finally sat me down and explained it. Let me save you years of bewilderment on this funny geek-speak term.
Instead of water, these days I think “result set” when I hear the term stream. It’s even easier to think of a stream as a table or a view. Just a big grid of data. In SQL Server terms, a stream resembles a table: the grid-like presentation of rows and columns that you get when you run a query. This is because SQL Server prefers to present data in a grid shape. SQL Server even produces spatial data first as a grid, although we humans prefer to view the result shaped as a sphere or map. However, XML does not set things up in rows and columns.
Tabular Stream
Let’s look at a simple SELECT statement from JProCo’s Location table and notice we get a table-like result set, also known as a tabular result set (see figure below). As noted above, the tabular stream is SQL Server’s preferred mode of streaming output from its data engine to your display.
XML Streams
Unlike SQL Server, XML actually has many modes of streaming output. You can instruct SQL Server to stream your XML result using the mode you prefer. Let’s look again at the data we queried from the Location table. This time we will have the output appear as an XML stream, rather than a tabular result.
XML Raw Mode
To our base query, we need to add the keywords “FOR XML” in order for our results to appear as XML. We also need to specify the mode, which in the figure below the raw mode.
We would say this result set now appears in an XML raw data format. Notice the result is a hyperlink, which you click to view your results. Also notice that the default tag label is “row” for each row of our result set and the data appear as attributes.
Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLInteropChapter1.0Setup.sql script from Volume 5.
Question 29
If you don’t specify any option, then XML RAW will have your data streamed in…
- Element text.
- Attributes.
Rules:
Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Every day one winner from India will get Joes 2 Pros Volume 5.
Every day one winner from United States will get Joes 2 Pros Volume 5.
The contest is open till next blog post shows up at which is next day GTM+2.5.
Reference: Pinal Dave (https://blog.sqlauthority.com)
78 Comments. Leave new
Attributes (option 2) is is the default result for XML Raw (when no other options are specified)
Country: United States
Option 2 Attributes is the answer
USA
Mike Michalicek
2.Attributes.
Gordon Kane
Allen TX
USA
it defaults to option 2 (attributes)
David
USA
Option # 2 Attributes is the right answer
Ritesh (India)
Answer is:
2.Attributes.
Vinay, Pune,
India
The correct option is #2
Attributes
Explanation: The default tag label is “row” for each row of our result set and the data appear as attributes.
Country of Residence: USA
Answer #2 Attributes is the correct answer.
Ashley Pace
correct answer for the question 29) “If you don’t specify any option, then XML RAW will have your data streamed in …” is
Option 2) Attributes.
WHY?
RAW mode transforms each row in the query result set into an XML element that has the generic identifier , or the optionally provided element name. By default, each column value in the rowset that is not NULL is mapped to an attribute of the element. If the ELEMENTS directive is added to the FOR XML clause, each column value is mapped to a subelement of the element. Together with the ELEMENTS directive, you can optionally specify the XSINIL option to map NULL column values in the result set to an element that has the attribute, xsi:nil=”true”.
You can request a schema for the resulting XML. Specifying the XMLDATA option returns an in-line XDR schema. Specifying the XMLSCHEMA option returns an in-line XSD schema. The schema appears at the start of the data. In the result, the schema namespace reference is repeated for every top-level element.
The BINARY BASE64 option must be specified in the FOR XML clause to return the binary data in base64-encoded format. In RAW mode, retrieving binary data without specifying the BINARY BASE64 option will result in an error.
EX:
For each row in the result set, the RAW mode generates an element . You can optionally specify another name for this element by specifying an optional argument to the RAW mode, as shown in this query. The query returns a element for each row in the rowset.
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID=122
FOR XML RAW (‘ProductModel’), ELEMENTS
GO
This is the result. Because the ELEMENTS directive is added in the query, the result is element-centric.
122
All-Purpose Bike Stand
Diljeet Kumari
Country : India
Answer is# 2
2.Attributes.
Country: India
Correct ans is option 2) attributes
in xml raw data streamed in attributes
shivakumar Lakkampelli
Mumbai, Maharastra
India.
Hi Pinal,
Challenge:
Question 29
If you don’t specify any option, then XML RAW will have your data streamed in…
1.Element text.
2.Attributes.
Correct Answer:
The correct choice is #2, Attributes.
Explanation:
From SQL Server 2008 Help:
RAW mode transforms each row in the query result set into an XML element that has the generic identifier , or the optionally provided element name. By default, each column value in the rowset that is not NULL is mapped to an attribute of the element.
Country:
United States
Thanks for the knowledge!
Regards,
Bill Pepping
the correct answer is option 2 i.e.
2. Attributes.
Kulwant kumar
delhi india
The correct option is #2
Attributes
Explanation: XML raw data streamlines in Attributes
Country of Residence: USA
Correct Answer : Option 2
If you don’t specify any option, then XML RAW will have your data streamed in…
2.Attributes
Regards
Jungchan Hsieh,
From Richardson, Texas, USA
2. Attributes.
Chetan – USA
The correct answer is attributes (Choice #2).
Ramdas
Country: USA
The correct Option is 2 (Attributes) as XML RAW display data in Attributes format.
(Sale, Nigeria)
The answer is 2: Attributes.
Pinal gives us the answer at the end of the blog post “…and the data appear as attributes”
it also says it here in Books Online: https://docs.microsoft.com/en-us/sql/relational-databases/xml/use-raw-mode-with-for-xml?view=sql-server-2017
#2 Attribute
Dan
NJ, USA