SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – What is XML? – Day 29 of 35

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.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - What is XML? - Day 29 of 35 J2P_29_1

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.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - What is XML? - Day 29 of 35 J2P_29_2

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.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - What is XML? - Day 29 of 35 J2P_29_3

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.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - What is XML? - Day 29 of 35 J2P_29_4

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - What is XML? - Day 29 of 35 J2P_29_5

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…

  1. Element text.
  2. 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)

Joes 2 Pros, SQL Scripts
Previous Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Structured Error Handling – Day 28 of 35
Next Post
SQL SERVER – SSQL Architecture Basics – Core Architecture Concepts – Book Available for SQL Server Certification

Related Posts

78 Comments. Leave new

  • Attributes (option 2) is is the default result for XML Raw (when no other options are specified)

    Country: United States

    Reply
  • Mike Michalicek
    August 29, 2011 6:40 pm

    Option 2 Attributes is the answer

    USA

    Mike Michalicek

    Reply
  • 2.Attributes.

    Gordon Kane
    Allen TX
    USA

    Reply
  • it defaults to option 2 (attributes)

    David
    USA

    Reply
  • Option # 2 Attributes is the right answer

    Ritesh (India)

    Reply
  • vinay (@vinayprasadv)
    August 29, 2011 7:08 pm

    Answer is:

    2.Attributes.

    Vinay, Pune,
    India

    Reply
  • 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

    Reply
  • Answer #2 Attributes is the correct answer.

    Ashley Pace

    Reply
  • Diljeet Kumari
    August 29, 2011 7:37 pm

    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

    Reply
  • Answer is# 2

    2.Attributes.

    Country: India

    Reply
  • shivakumarlakkampelli
    August 29, 2011 8:10 pm

    Correct ans is option 2) attributes

    in xml raw data streamed in attributes

    shivakumar Lakkampelli
    Mumbai, Maharastra
    India.

    Reply
  • 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

    Reply
  • the correct answer is option 2 i.e.

    2. Attributes.

    Kulwant kumar
    delhi india

    Reply
  • Srinivasan Prasanna
    August 29, 2011 9:04 pm

    The correct option is #2

    Attributes

    Explanation: XML raw data streamlines in Attributes

    Country of Residence: USA

    Reply
  • Jungchan Hsieh
    August 29, 2011 9:20 pm

    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

    Reply
  • 2. Attributes.

    Chetan – USA

    Reply
  • The correct answer is attributes (Choice #2).
    Ramdas
    Country: USA

    Reply
  • The correct Option is 2 (Attributes) as XML RAW display data in Attributes format.

    (Sale, Nigeria)

    Reply
  • 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

    Reply
  • #2 Attribute

    Dan
    NJ, USA

    Reply

Leave a Reply