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

« SQL SERVER – SSQL Architecture Basics – Core Architecture Concepts – Book Available for SQL Server Certification

SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Using Root With Auto XML Mode – Day 31 of 35 »

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

August 30, 2011 by pinaldave

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?

They error out since XSINIL does not allow nulls.
They appear as empty tags.
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

Posted in Joes 2 Pros, Pinal Dave, PostADay, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 89 Comments

89 Responses

on August 30, 2011 at 7:15 am | Reply Kalyanasundaram.K

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
on August 30, 2011 at 7:34 am | Reply Rene Alberto Castro Velasquez

Correct answer is No. 3: without XSINIL, no tags are present for null values.
Rene Castro
El Salvador
on August 30, 2011 at 7:40 am | Reply Santosh.S

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
on August 30, 2011 at 7:43 am | Reply Gopalakrishnan Arthanarisamy

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.
on August 30, 2011 at 8:07 am | Reply Naomi

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
on August 30, 2011 at 8:12 am | Reply Asim Khan

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
on August 30, 2011 at 8:14 am | Reply Asim Khan

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.
on August 30, 2011 at 8:17 am | Reply Sai Sharat

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
on August 30, 2011 at 8:48 am | Reply Uday Bhoopalam

correct answer option 3. Tags are missing for the null record.

Thank You
Uday
USA
on August 30, 2011 at 8:52 am | Reply Reddy

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
on August 30, 2011 at 9:06 am | Reply Bharat Patel

The answer is 3 (No tags are present for null values.)
on August 30, 2011 at 9:24 am | Reply Nikhil Mahajan

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
on August 30, 2011 at 9:31 am | Reply Aditya Bisoi (@AdityaBisoi07)

Question 30
Ans : # 3 —
No tags are present for null values.

Chennai ,INDIA
on August 30, 2011 at 9:36 am | Reply gvprabu

Correct Answer is Option : 3 No tags are present for null values.

– Venkatesh Prabu G || Bangalore || India
on August 30, 2011 at 9:58 am | Reply arjunyadav89

Without XSINIL, No tags are present for null values.

option 3 is correct.

Arjun

Hyderabad,INDIA
on August 30, 2011 at 10:02 am | Reply Sunil (India)

Answer is Option# :: 3
on August 30, 2011 at 10:17 am | Reply sreeram

Options # 3.

No tags are present for null values.

Sreeram
India
on August 30, 2011 at 10:20 am | Reply Sowjanya

The correct answer is option 3
No tags are present for null values.

Sowjanya,
INDIA
on August 30, 2011 at 10:30 am | Reply Kewal Khanna

Correct Answer : Option 3

No tags are present for null values.

Kewal Khanna, India
on August 30, 2011 at 10:35 am | Reply Sumit

Correct answer is option 3) No tags are present for null values.

Sumit
India
on August 30, 2011 at 10:45 am | Reply Geetika Bhambri

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
on August 30, 2011 at 10:53 am | Reply Kaushik Modi

Hi,

Correct Option is 3). No tags are present for null values.

I am from India.
on August 30, 2011 at 10:54 am | Reply Manjula T

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
on August 30, 2011 at 11:05 am | Reply Mahmad Khoja

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
on August 30, 2011 at 11:10 am | Reply Shatrughna Kumar

Correct answer is #3.
3.No tags are present for null values.
It is very clear from this article.

New Delhi
India
on August 30, 2011 at 11:14 am | Reply Fazal Vahora

Correct Answer is Option 3.

3.No tags are present for null values.

Country:India

Thanks,
Fazal Vahora
on August 30, 2011 at 11:22 am | Reply Sandhya

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
on August 30, 2011 at 11:22 am | Reply ghanshyam

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
on August 30, 2011 at 11:38 am | Reply Narendra

Option – 3 is the corret answer for this Question

Thanks,
Narendra(India).
on August 30, 2011 at 11:38 am | Reply P.Anish Shenoy

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
on August 30, 2011 at 11:40 am | Reply Sale A. A.

Correct Option is 3. No tags are present for null values.

This because without XSINIL only sub-elements with not null value will be shown.

(Sale, Nigeria)
on August 30, 2011 at 11:41 am | Reply maanshah

Correct answer: Option 3

As, XSINIL make null explicitly specified, not tags will be preset for null values.

Mansi Shah
India
on August 30, 2011 at 12:00 pm | Reply kkmjssate

correct Answer:3)No tags are present for null values.
but XSINIL means i do not know ?
which is special keyword ;(mystryalso)
krishan kumar mishra
India
on August 30, 2011 at 12:10 pm | Reply Shalini.M

Answer: Option 3.
No tags are present for null values.

ShaliniMeyyappan
India
on August 30, 2011 at 12:12 pm | Reply Shekhar Gurav

Correct answer option no – 3

3) No tags are present for null values.

Shekhar Gurav.
Country : India
on August 30, 2011 at 12:17 pm | Reply Partha Pratim Dinda

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
on August 30, 2011 at 12:22 pm | Reply Madhusmita Rout

Answer is option 3: No tag present for null values
on August 30, 2011 at 12:40 pm | Reply riteshshahsh

answer is clearly described in last two para of the article.

right answer is option # 3

No tags are present for null values.

Ritesh (India)
on August 30, 2011 at 1:25 pm | Reply Nakul Vachhrajani

The correct answer is #3: No tags are present for null values.
Country of residence: India
on August 30, 2011 at 1:29 pm | Reply Praveen M

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

Regards,
Praveen M
on August 30, 2011 at 1:35 pm | Reply Rahul

The correct option is:
3

Rahul Sharma
Noida India
on August 30, 2011 at 1:39 pm | Reply Hema Chaudhry

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
on August 30, 2011 at 1:54 pm | Reply sudeepta

The correct answer is :
3. No tags are present for null values

Sudeepta,
India.
on August 30, 2011 at 1:54 pm | Reply Richa Aggarwal

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
on August 30, 2011 at 2:09 pm | Reply Mitesh Modi

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)
on August 30, 2011 at 2:20 pm | Reply Hiren Bavishi

Correct answer option no – 3

3) No tags are present for null values.

Hiren Bavishi
India
on August 30, 2011 at 2:30 pm | Reply vaishali jain

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.
on August 30, 2011 at 2:35 pm | Reply Nikhildas

Answer Option 3

No tags are present for null values.

Nikhildas
cochin
INDIA
on August 30, 2011 at 2:38 pm | Reply Pratik Raval

Correct answer is option 3.

3) No tags are present for null values.

Pratik Raval
India
on August 30, 2011 at 2:38 pm | Reply Rahul Dewada

The Answer is #3
No tags are present for null values

Country : India
on August 30, 2011 at 4:33 pm | Reply madhivanan

Answer 3

3.No tags are present for null values.

Country : India
on August 30, 2011 at 5:06 pm | Reply Mike Michalicek

Answer #3

USA

Mike Michalicek
on August 30, 2011 at 5:19 pm | Reply SQL SERVER – Win a Book a Day – Contest Rules – Day 0 of 35 Journey to SQLAuthority

[...] Q 30) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – What is XML? – Day 30 of 35 [...]
on August 30, 2011 at 5:30 pm | Reply Ron A. Farris

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
on August 30, 2011 at 5:39 pm | Reply Bill Pepping

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
on August 30, 2011 at 5:44 pm | Reply Don

Without XSINIL, no tags will be present for null values (option 3).

Country: United States
on August 30, 2011 at 5:57 pm | Reply Deb

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
on August 30, 2011 at 5:58 pm | Reply Varinder Sandhu

correct option is 3 as

No tags are present for null values.

Varinder Sandhu (India)
on August 30, 2011 at 6:09 pm | Reply Geetanjali Agarwal

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
on August 30, 2011 at 6:22 pm | Reply Neeraj Lohumi

correct answer is #3
3.No tags are present for null values.

India (New Delhi)
Neeraj Lohumi
on August 30, 2011 at 7:15 pm | Reply Rajesh Mohanrangan

Correc Answer 3

3.No tags are present for null values.

Regards
Rajesh
From india
on August 30, 2011 at 7:28 pm | Reply Hina Shingala

Answer #3

Hina Shingala
USA
on August 30, 2011 at 7:48 pm | Reply Manish Choudhary

Answer 3 is correct-No tags are present for null values
on August 30, 2011 at 7:49 pm | Reply Chetan

No tags are present for null values.

Chetan – USA
on August 30, 2011 at 8:01 pm | Reply levpius

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

Leo Pius
USA
on August 30, 2011 at 8:20 pm | Reply Diljeet Kumari

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
on August 30, 2011 at 9:02 pm | Reply Kevin Scharnhorst

3. No tags are present for null values.

USA

Kevin Scharnhorst
on August 30, 2011 at 9:20 pm | Reply Rishi Divecha

Correct answer: option 3

Rishi Divecha
Iselin, NJ USA.
on August 30, 2011 at 9:46 pm | Reply Hiren Visavadiya

Answer#3
No tags are present for null values.

–hiren visavadiya
(India)
on August 30, 2011 at 10:46 pm | Reply Rajneesh Verma

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

Thanks…
Rajneesh Verma
(INDIA)
on August 30, 2011 at 11:36 pm | Reply David Brust

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
on August 30, 2011 at 11:57 pm | Reply Matt Nelson

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
on August 31, 2011 at 12:29 am | Reply Sowmiya Duraisamy

Answer is : 3

No Tags are present for null values.

Country:USA
Sowmiya Duraisamy
on August 31, 2011 at 12:32 am | Reply dilipkumarjena

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
on August 31, 2011 at 3:13 am | Reply Basavaraj

Correct Answer Is: option 3

Thanks,
Basavaraj
India
on August 31, 2011 at 3:34 am | Reply Ramdas

Answer #3

Ramdas,NC
USA
on August 31, 2011 at 7:44 am | Reply sravan

Correct Option is 3

INDIA
on August 31, 2011 at 10:47 am | Reply Jayesh Varu

3) 3rd option is correct

JD Varu
Ahmedabad – India
on August 31, 2011 at 11:47 am | Reply Anugrah

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

INDIA
on August 31, 2011 at 12:04 pm | Reply vishal patwardhan (@vishalpatwardha)

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
on August 31, 2011 at 12:28 pm | Reply Raushan Kumar Jha

Ans 3.

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

India
on August 31, 2011 at 5:52 pm | Reply vinay (@vinayprasadv)

Answer is :

3.No tags are present for null values.

Vinay,
Pune, India.
on August 31, 2011 at 6:29 pm | Reply matt rides

Answer#3
No tags are present for null values.

USA
on August 31, 2011 at 6:40 pm | Reply Robin Thomas

correct option is 3

Robin Thomas
India
on August 31, 2011 at 7:20 pm | Reply David Seefeld

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
on August 31, 2011 at 7:21 pm | Reply Gordon Kane

3.No tags are present for null values.

Gordon Kane
Allen TX
USA
on September 3, 2011 at 12:00 pm | Reply pinaldave

A.) 3.No tags are present for null values.

Winner from USA: Sai Sharat

Winner from India: Reddy

I thank you all for participating here. The permanent record of this update is posted on facebook page.
on September 7, 2011 at 7:43 am | Reply SQL SERVER – A Guide to Integrating SQL Server with XML, C#, and PowerShell – Book Available for SQL Server Certification Journey to SQLAuthority

[...] Q 30) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – What is XML? – Day 30 of 35 [...]
on August 10, 2012 at 7:02 am | Reply SQL SERVER – Understanding XML – Contest Win Joes 2 Pros Combo (USD 198) – Day 5 of 5 « SQL Server Journey with SQL Authority

[...] SQL Joes 2 Pros Development Series – What is XML? – 2 [...]