SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – What is XML? – Day 30 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.

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.

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

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.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - What is XML? - Day 30 of 35 j2p_30_2
SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - What is XML? - Day 30 of 35 j2p_30_3

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”

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - What is XML? - Day 30 of 35 j2p_30_4
SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - What is XML? - Day 30 of 35 j2p_30_5

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”

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - What is XML? - Day 30 of 35 j2p_30_6
SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - What is XML? - Day 30 of 35 j2p_30_7

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

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - What is XML? - Day 30 of 35 j2p_30_8
SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - What is XML? - Day 30 of 35 j2p_30_9

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.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - What is XML? - Day 30 of 35 j2p_30_10
SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - What is XML? - Day 30 of 35 j2p_30_11

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.

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

,
Previous Post
SQL SERVER – SSQL Architecture Basics – Core Architecture Concepts – Book Available for SQL Server Certification
Next Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Using Root With Auto XML Mode – Day 31 of 35

Related Posts

86 Comments. Leave new

  • Answer #3

    Hina Shingala
    USA

    Reply
  • Manish Choudhary
    August 30, 2011 7:48 pm

    Answer 3 is correct-No tags are present for null values

    Reply
  • No tags are present for null values.

    Chetan – USA

    Reply
  • Correct answer is r is 3
    without XSINIL, no tags are present for null values.

    Leo Pius
    USA

    Reply
  • Diljeet Kumari
    August 30, 2011 8:20 pm

    The answer for the above question is #3

    No tags are present for null values.

    As very well explained by you

    Some more Explanation :

    The ELEMENTS directive constructs XML in which each column value maps to an element in the XML. If the column value is NULL, no element is added. By specifying the optional XSINIL parameter on the ELEMENTS directive, you can request that an element also be created for the NULL value. In this case, an element that has the xsi:nil attribute set to TRUE is returned for each NULL column value.

    We can override the schema declaration when setting xsi:nil attribute to value “true”. The schema must allow this by setting attribute “nillable” to “true”

    Diljeet kumari
    Country : INIDA

    Reply
  • Kevin Scharnhorst
    August 30, 2011 9:02 pm

    3. No tags are present for null values.

    USA

    Kevin Scharnhorst

    Reply
  • Correct answer: option 3

    Rishi Divecha
    Iselin, NJ USA.

    Reply
  • Hiren Visavadiya
    August 30, 2011 9:46 pm

    Answer#3
    No tags are present for null values.

    –hiren visavadiya
    (India)

    Reply
  • Rajneesh Verma
    August 30, 2011 10:46 pm

    Option 3 is the correct answer.
    “No tags are present for null values”

    Thanks…
    Rajneesh Verma
    (INDIA)

    Reply
  • If you don’t specify the XSINIL option for your XML stream, null values will be expressed as absent tags.

    Answer 3

    David Brust
    USA

    Reply
  • The answer is #2.
    If there is a null value in the result set, the XML will not generate a row for that Null by default.

    Matt Nelson, USA

    Reply
  • Sowmiya Duraisamy
    August 31, 2011 12:29 am

    Answer is : 3

    No Tags are present for null values.

    Country:USA
    Sowmiya Duraisamy

    Reply
  • dilipkumarjena
    August 31, 2011 12:32 am

    correct answer is 3) No tags are present for null values

    When we dont specify XSINIL the result set having null values will be removed from the XML Data and no tags are formed.

    DILIP KUMAR JENA
    Country INDIA

    Reply
  • Correct Answer Is: option 3

    Thanks,
    Basavaraj
    India

    Reply
  • Answer #3

    Ramdas,NC
    USA

    Reply
  • Correct Option is 3

    INDIA

    Reply
  • 3) 3rd option is correct

    JD Varu
    Ahmedabad – India

    Reply
  • Option3 “No tags are present for null values.” is correct.

    INDIA

    Reply
  • vishal patwardhan (@vishalpatwardha)
    August 31, 2011 12:04 pm

    HI All,

    accoring to me correct answer is option 3.

    explanation : tags are not present for null values but in place of display values in tags this displays the following :xsi:nil=”true” which tell us that following value is null in the source from where xml is generated.

    Vishal Patwardhan
    (Indore),India

    Reply
  • Raushan Kumar Jha
    August 31, 2011 12:28 pm

    Ans 3.

    Without “XSINIL”
    3.No tags are present for null values.

    India

    Reply

Leave a Reply

Menu