SQL XML .node .query() returns empty string instead of NULL if table node exist

user_11715851 0
Viewed 1792

Why does .query() return empty string instead of NULL if the table doesn't exist? I am new to querying xml in SQL so I am just playing around and this was unexpected.

Here is an example of the problem.

declare @xml xml

set @xml = N'<xdoc><Header><OrderID>1234</OrderID><Detail><ProductID>12345</ProductID><Amount>12.50</Amount></Detail></Header></xdoc>'

SELECT  
    Tbl.Col.value('OrderID[1]', 'varchar(10)') as OrderID,  
    Tbl.Col.query('Detail') as Detail
FROM @xml.nodes('//Header') Tbl(Col)

set @xml = N'<xdoc><Header><OrderID>1234</OrderID></Header></xdoc>'

SELECT  
    Tbl.Col.value('OrderID[1]', 'varchar(10)') as OrderID,  
    Tbl.Col.query('Detail') as Detail
FROM @xml.nodes('//Header') Tbl(Col)

The first select statement returns as expected. The Detail field is the xml of the table detail within the header.

However, on the second select statement this header record did not have a detail table so I expected the field to be null instead of empty string.

Output

xml-parsing xml sql-server sql
1 Answers

What you want is possible, but you must think a little bit differently. In XML the value NULL is indicated by a missing node.

Furthermore, the word "value" is very important in this sentence. The .value() method will return NULL if the element you are looking for is not present. The .query()-method will return a XML-typed fragment in any case.

XML consits of nodes. Elements (any <SomeElement> together with all nested nodes) are a special kind of node. The floating text within an element is a node in itself. Look at these examples:

Your XML

declare @xml xml

set @xml = 
N'<xdoc>
  <Header>
    <OrderID>1234</OrderID>
    <Detail>
      <ProductID>12345</ProductID>
      <Amount>12.50</Amount>
    </Detail>
  </Header>
</xdoc>'

--.query() can return the <Detail>-fragment

SELECT @xml.query('/xdoc/Header/Detail');

<Detail>
  <ProductID>12345</ProductID>
  <Amount>12.50</Amount>
</Detail>

--This node does not exist. What you get back is not a NULL-value but an empty XML node

SELECT @xml.query('/xdoc/Header/Dummy');                                       --<-- empty XML

--Now look at .value()

SELECT @xml.value('(/xdoc/Header/Dummy)[1]','nvarchar(100)');                  --<-- NULL
SELECT @xml.value('(/xdoc/Header/Detail)[1]','nvarchar(100)');                 --<-- 1234512.50
SELECT @xml.value('(/xdoc/Header/Detail/text())[1]','nvarchar(100)');          --<-- NULL
SELECT @xml.value('(/xdoc/Header/Detail/ProductID)[1]','nvarchar(100)');       --<-- 12345
SELECT @xml.value('(/xdoc/Header/Detail/ProductID/text())[1]','nvarchar(100)');--<-- 12345

The first call is clear, there is no <Dummy>, therefore, we get a NULL back.
The second call might surprise you. The value is everything within the element. In this case we get a combination of all the values. You might have seen this as a trick to simulate group_concat().
The third call returns NULL as there is no text() within <Detail>
... and the fourth and the fifth call return the same, but the last one with text() is most specific and therefore the best choice.

It is depending on your needs, what you want to query and what you want to get back. Furthermore, you can use predicates like empty(SomeNode) or not(empty(SomeNode)) to use XQuery-filters in your XPath.

user_50892040
Related