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 which is next day GTM+2.5.

Reference:  Pinal Dave (https://blog.sqlauthority.com)