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.

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…

  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 http://blog.sqlauthority.com which is next day GTM+2.5.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

About these ads

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

  1. The answer to this question is 2 – Attributes and it’s is explained towards the end of this article:

    Quote: Also notice that the default tag label is “row” for each row of our result set and the data appear as attributes.

    Thanks for the helpful series.

    I am from USA

  2. Hi,

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

    Thanks

    Sudhir Chawla
    New Delhi, India

  3. Correct answer is No. 2: if I don’t specify any option, then XML RAW will have my data streamed in attributes, similar (but not equal) to an XML file.
    Rene Castro
    El Salvador

  4. Answer : Option 1

    If you don’t specify any option, then XML RAW will have your data streamed in…

    1) Element Text

    Chennai, TamilNadu, India

  5. Pingback: SQL SERVER – Win a Book a Day – Contest Rules – Day 0 of 35 Journey to SQLAuthority

  6. If no option is specified for “RAW XML”, then data appears as attributes. So, Option 2 is correct.

    2. Attributes.

    Regards,
    Geetika Bhambri
    Ahmedabad(Gujarat)-INDIA

  7. Hi,
    if we read description carefully then we will get the para as:
    “Also notice that the default tag label is “row” for each row of our result set and the data appear as attributes.” so its clearly says that answer is 2 ie.
    2) Attributes.

    Thanks..
    Rajneesh Verma
    (INDIA)

  8. The correct answer is #2 – Attributes

    FOR XML RAW will have data streamed (Great conceptual explaination of streams) as Attributes if no options are provided. The article made it quite easy to answer this one :)

    Country of residence: India

  9. The Correct Answer is :

    2.Attributes.

    Explanation:

    The tabular stream is SQL Server’s preferred mode of streaming output from its data engine to your display. Table-like result set is the output and hence the correct answer is the “Attributes”.

    Country:

    India

  10. Ans is 2:
    if I don’t specify any option, then XML RAW will have data streamed in attributes

    Though the default tag label is “row” for each row of our result set and the data appear as attributes.

    Partha,
    India

  11. Answer is: 2. Attributes. Because this is the first preffered option for XML RAW streaming, to view as Metatadata and Data.

    Ron A. Farris
    USA

  12. Answer is #2

    2.Attributes.

    In the example provided you can clearly see that the data coming back is a row per record with all the “column data” listed as attributes with values; it is not in element form, therefore the answer is Attributes.

    USA

  13. Hi Sir,

    2. Attributes
    is the correct answer.

    By Default each column value (i.e the data) in the rowset that is not NULL is mapped to an attribute of the element

    as said in the blog the default tag label is “row” for each row of the result set and the data appear as attributes.

    Therefore If we don’t specify any option, then XML RAW will have our data streamed in “Attributes”.

    Thanks for the knowledge i dint know much about this.

    Regards,
    P.Anish Shenoy,
    INDIA,Bangalore, Karnataka.

  14. 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

  15. 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

  16. The correct option is #2

    Attributes

    Explanation: XML raw data streamlines in Attributes

    Country of Residence: USA

  17. Correct Ans : 2

    2. Attributes

    Some more points to keep in mind:

    a. If we’re using “FOR XML RAW”
    e.g.
    Query : select top(1) CustomerID, CustomerName from TestCustomers
    FOR XML RAW
    Output :

    b. If we want come customized name instead of “row” in the result set specify the name.
    e.g.
    Query : select top(1) CustomerID, CustomerName from TestCustomers
    FOR XML RAW (‘CustomerInfo’)
    Output :

    c. If we want result in element text instead of attribute try this (use PATH instead of RAW)
    e.g.
    Query : select top(1) CustomerID, CustomerName from TestCustomers
    FOR XML Path (‘CustomerInfo’)

    Output :
    0000001
    Shri Ram

  18. Answer: Attribute

    The default tag label is “row” for each row of our result set and the data appear as attributes.

    Aditi
    USA

  19. Pingback: SQL SERVER – A Guide to Integrating SQL Server with XML, C#, and PowerShell – Book Available for SQL Server Certification Journey to SQLAuthority

  20. Pingback: SQL SERVER – Understanding XML – Contest Win Joes 2 Pros Combo (USD 198) – Day 5 of 5 « SQL Server Journey with SQL Authority

  21. Pingback: SQL SERVER – Weekly Series – Memory Lane – #044 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s