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 which is next day GTM+2.5.
Reference: Pinal Dave (https://blog.sqlauthority.com)
86 Comments. Leave new
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
Hi,
Correct Option is 3). No tags are present for null values.
I am from India.
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
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
Correct answer is #3.
3.No tags are present for null values.
It is very clear from this article.
New Delhi
India
Correct Answer is Option 3.
3.No tags are present for null values.
Country:India
Thanks,
Fazal Vahora
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
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
Option – 3 is the corret answer for this Question
Thanks,
Narendra(India).
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
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)
Correct answer: Option 3
As, XSINIL make null explicitly specified, not tags will be preset for null values.
Mansi Shah
India
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
Answer: Option 3.
No tags are present for null values.
ShaliniMeyyappan
India
Correct answer option no – 3
3) No tags are present for null values.
Shekhar Gurav.
Country : India
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
Answer is option 3: No tag present for null values
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)
The correct answer is #3: No tags are present for null values.
Country of residence: India
Answer is 3: No tags are present for null values.
Regards,
Praveen M