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.

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

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

About these ads

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

  1. Correct Answer : Option 3

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

    3) No tags are present for null values.

    Chennai, TamilNadu, India

  2. Correct answer is No. 3: without XSINIL, no tags are present for null values.
    Rene Castro
    El Salvador

  3. Hi

    Option 3) is the right answer

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

    Thanks & Regards
    Santosh.S
    Bangalore, India

  4. Correct Answer is # 3.

    3. No tags are present for null values.

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

    Gopalakrishnan Arthanarisamy
    Unisys, Bangalore, India.

  5. The answer to this question is #3
    No tags are present for null values.

    This is well explained in this great article

    I am from USA

  6. Answer.

    3. No tags are present for null values.

    ……

    The XSINIL argument is used to display the NULL values in the XML output. It can be used with the FOR XML clause and the ELEMENTS directive.

    The following example will demonstrate the use of the XSINIL argument:

    SELECT AuthID,AuthName FROM Authors FOR XML RAW, ELEMENTS XSINIL

  7. Answer.

    3. No tags are present for null values.

    ……

    The XSINIL argument is used to display the NULL values in the XML output. It can be used with the FOR XML clause and the ELEMENTS directive.

    The following example will demonstrate the use of the XSINIL argument:

    SELECT AuthID,AuthName FROM Authors FOR XML RAW, ELEMENTS XSINIL
    …………………………….

    Country : India.

    had to re post this as forgot to mention the country of residence.

  8. The correct option is option 3
    No tags are present for Null Values. The exact and accurate representation of null values in XML is no displaying the tags with null values. If we still insist to get the null value tags we can use XSINIL.

    -Sai
    USA

  9. Correct answer is option #3.

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

    3.No tags are present for null values.

    As explained clearly in the article above.

    Thanks.

    Country – India

  10. the correct answer is option 3. i.e.

    3. No tags are present for null values.

    because if we dont write xsinil then xml return wont contain null value hence that element will be missed as explained by you in this post…

    India

  11. Correct Option is 3, i.e.

    3. No tags are present for null values.

    Reason:
    In SQL Server,the fields which have null value for some records but are populated with regular values,seem to have missing tags for the null record. The XSINIL option allows to force an XML tag to be present for every field in the query, even if the underlying data has NULL field values.

    Regards,
    Geetika Bhambri
    Ahmedabad(Gujarat)-INDIA

  12. The Correct Option is:

    3.No tags are present for null values.

    Explanation:

    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. Without XSINIL, the null values of our result set will not have any corresponding tag present in it.

    Country:

    India

  13. The correct answer is option 3

    As per the given explaination in this post. If we are not using XSINIL keyword then the element which has NULL value will not be appeared in resulting XML.

    Mahmad Khoja
    Country : INDIA

  14. If the XSINIL keyword is not added, the Null values are not displayed. In order for each XML tag to include all the columns as elements irrespective of null/non-null values, we must include the XSINIL keyword. The correct option is:
    3. No tags are present for null values.

    Sandhya
    Bangalore,India

  15. option 3.

    As per your discussion if a rows value is null then it will not appear in the tag.
    by using the option xsinil we will get the result as

    ghanshyam
    india

  16. Hi Sir,

    3) No tags are present for null values

    is the correct answer.

    As explained in the blog above the XSINIL option allows us to force an XML tag to be present for every field in the query, even if the underlying data has NULL field values.

    Therefore without XSINIL option the tags will not be present for NULL field values.

    Thanks for the knowledge.

    P.Anish Shenoy,
    INDIA

  17. Ans is 3.
    No tags are present for null values.

    Because xml element constructs xml such a manner that each column value maps to an element in the XML. If the column value is NULL, no element is added and it is presume to null.
    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.(From this article).

    Partha,
    India

  18. Hi,

    The correct answer is Option 3.

    Explanation: For fields in SQL Server which include a null value for some records but are populated with regular values in the other records, will have missing tags for the null record.

    The XSINIL option allows to force an XML tag to be present for every field in the query, even if the underlying data has NULL field values.

    Thanks,
    Hema Chaudhry
    Delhi, India

  19. Option 3 is the right answer.

    Explanation – Without XSINIL option, SQL server removes xml tags from columns having null values. XSINIL option forces SQL Server to insert xml tags for columns having null values.

    Country – USA

  20. ANS : 3
    (3) No tags are present for null values.

    Explanation
    =========
    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.
    Without XSINIL no tags are present for null values.

    Thanks,
    Mitesh Modi
    (India)

  21. Correct Answer isOption 3

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

    correct Answer: 3) No tags are present for null values.

    Hyderabad, Andhra Pradesh, India.

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

  23. The Answer is: 3. No tags are present for null values.

    Without XSINIL to generate a tag, the missing third sub-element would throw an error if that is what the code or company policy called for.

    Ron A. Farris – USA

  24. Hi Pinal,

    Challenge:
    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.

    Correct Answer:
    The correct choice is #3: No tags are present for null values.

    Explanation:
    As Pinal showed in the bog, when XSINIL ins’t included in the SQL, data elements that contain NULL will be excluded from XML result set (stream).

    Country:
    United States

    Thanks for the knowledge!

    Regards,

    Bill Pepping

  25. The answer is #3

    No tags are present for null values.

    As explained in your excellent article, when there is a null value for a field in your select, you will seem to be missing tags for the null record. If for some reason you do not want null tags to be missing, you need to use the XSINIL option which will force a tage to be present for each field in the query.

    Thanks!
    Deb – USA

  26. Correct answer is 3:
    No tags are present for null values.

    Reason – Without XSINIL option, SQL server removes xml tags from columns having null values.

    Thanks,
    Geetanjali Agarwal
    India

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

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

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

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

  31. The correct option is #3

    No tags are present for null values.

    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.

    Country of Residence: USA

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

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

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