SQL Server Parse XML empty property

Question

I'm trying to parse / shred XML in sql server 2008 via TSQL, one of the nodes has a blank property in the following format with no value specified. I can't seem to retrieve that value and it throws off all the others (shifts the columns ?)

Here is the tsql code and a pic of what is wrong:

declare @doc nvarchar(4000)

set @doc = ' 
<Objects>
  <Object>
    <Property Name="Path">some path</Property>
    <Property Name="InstanceName">some instance</Property>
    <Property Name="result1">0.390630000000003</Property>
    <Property Name="result2">63345649697265</Property>
  </Object>
  <Object>
    <Property Name="Path">another path</Property>
    <Property Name="InstanceName" />
    <Property Name="result1">100</Property>
    <Property Name="result2">1002</Property>
  </Object>
 </Objects>
'
  SELECT 
 item.ref.value('(Property/text())[1]', 'nvarchar(128)') AS Path, 
 item.ref.value('(Property/text())[2]', 'nvarchar(128)') AS InstanceName,
 item.ref.value('(Property/text())[3]', 'nvarchar(128)') AS result1,
 item.ref.value('(Property/text())[4]', 'nvarchar(128)') AS result2
  FROM (SELECT CAST(@doc AS XML) AS feedXml) feeds(feedXml) 
   CROSS APPLY feedXml.nodes('/Objects/Object') AS item(ref)

When you run the query notice that the column InstanceName is populated with the value from the other column instead of blank, empty or null.

Any help is appreciated.

marc_s · Accepted Answer · 2012-11-12 06:02:25Z

2

You need to reference those <Property> elements by using the Name attribute - not just the index!

Try this:

SELECT
    item.ref.value('(Property[@Name="Path"]/text())[1]', 'nvarchar(128)') AS Path,
    item.ref.value('(Property[@Name="InstanceName"]/text())[1]', 'nvarchar(128)') AS InstanceName,
    item.ref.value('(Property[@Name="result1"]/text())[1]', 'nvarchar(128)') AS result1,
    item.ref.value('(Property[@Name="result2"]/text())[1]', 'nvarchar(128)') AS result2
FROM 
    (SELECT CAST(@doc AS XML) AS feedXml) feeds(feedXml) 
CROSS APPLY 
    feedXml.nodes('/Objects/Object') AS item(ref)

With this, you should get your second InstanceName as a NULL.

answered Nov 12, 2012 at 6:02

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:18:42Z

Within the bracket you specify what node you want so [4] means that you want the fourth row. The XPath expression you use is Property/text() so that means that '(Property/text())[4]' will give you the fourth text value. Your second object has only three text values and that is the reason your values are off.
Instead you can specify that you want the the first text value from the fourth Property node '(Property[4]/text())[1]'.

Full query could look something like this:

SELECT 
 item.ref.value('(Property[1]/text())[1]', 'nvarchar(128)') AS Path, 
 item.ref.value('(Property[2]/text())[1]', 'nvarchar(128)') AS InstanceName,
 item.ref.value('(Property[3]/text())[1]', 'nvarchar(128)') AS result1,
 item.ref.value('(Property[4]/text())[1]', 'nvarchar(128)') AS result2
  FROM (SELECT CAST(@doc AS XML) AS feedXml) feeds(feedXml) 
   CROSS APPLY feedXml.nodes('/Objects/Object') AS item(ref)

The above will of course still fail if you have property nodes missing or if the order of the nodes is not consistent. In that case you should use the query provided by marc_s.

Collectives™ on Stack Overflow

SQL Server Parse XML empty property

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related