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

What is XML Day 30 builds on the basics with raw mode and the ROOT keyword. FOR XML RAW turns each row of a query result into an XML element. 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.

Let’s look at another example from the Employee table.  If you ran the reset script for this chapter, you should see 14 JProCo employees showing in your Employee table.

Next we will add FOR XML RAW to view the result from the Employee table as an XML output using the raw mode. We have changed our Employee table result to output as XML RAW. Notice that every row of our XML RAW output is labeled “row” by default.

We next will add a root to our output. We will add the keyword ROOT to our existing code (see figure below) and then look at our revised output.  We are adding the keyword ROOT in order to see a root node in our XML output. We now see the root node (a.k.a., the root element). Not only is our output more readable and organized, but this is considered “well-formed XML”

Now let’s put the data into elements. We can see each employee now has three sub-elements under the top element, which is “row”

Each row has three child elements (FirstName, LastName, and LocationID). The exception is John Marshbank, who only has two elements. If we query the Employee table, we quickly see the reason for this is that John Marshbank is the only one with a NULL LocationID. John Marshbank has just two sub-elements beneath the top element, “row”.

Our mystery is solved – we understand John Marshbank’s having just two data sub-elements is caused by his LocationID value having a NULL value.  Suppose the program which needs to consume our result requires three data sub-elements. Or suppose company policy specifies that each employee record must contain three data sub-elements. John Marshbank’s record doesn’t meet the criteria and would thus be in violation of the policy.

XSINIL

For fields in SQL Server which include a null value for some records but are populated with regular values in the other records, you will seem to have missing tags for the null record. Often this is alright, as missing tags are presumed to be null. The XSINIL option allows you to force an XML tag to be present for every field in the query, even if the underlying data has NULL field values. Our next example will show us how to make a LocationID tag appear for John Marshbank.

If you require all tags to be present (even if they have no data), then you can specify the XSINIL option for your XML stream. The XSINIL option will force tags to be present for all fields of all records, including those which contain null values. Let’s rerun our prior code and add the XSINIL option.

We now see a third sub-element for John Marshbank. The LocationID tag is no longer missing. It is present and shows the value xsi:nil=“true” in place of a LocationID. Our objective has been met:  John Marshbank’s record now includes three data elements thanks to XSINIL.

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 30

Without XSINIL, what happens to null values from your result set?

1. They error out since XSINIL does not allow nulls.
2. They appear as empty tags.
3. No tags are present for null values.

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.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.