CODEWITHSUNDEEP
sql-serversql-server-2008xquery-sql
n8.
n8.

Reputation: 1738

Querying an XML column - returning relational data

I have a problem where I must return data in a SQL query that is contained in an XML column. I have some tentative results, but am having a difficult time making quick headway. So far I have this:

XML (example for one row):

<root>
  <property>
    <Name>Boolean</Name>
    <Value>True</Value>
  </property>
  <property>
    <Name>Integer</Name>
    <Value>0</Value>
  </property>
</root>

SQL:

select ItemID,
       boolean = CASE WHEN CF.exist('/root/property/Name[text() = "Boolean"]') = 1 
        THEN CF.value('(/root/property/Value)[1]', 'varchar(32)') END,
       [integer] = CASE WHEN CF.exist('/root/property/Name[text() = "Integer"]') = 1 
        THEN CF.value('(/root/property/Value)[2]', 'varchar(32)') END
from 
       [TTS].[dbo].[tblInItem]

And the resulting data is this:

enter image description here

You can see that row 4 is populating a boolean where there should be an integer. This is because the XML for that line is:

<root>
  <property>
    <Name>Boolean</Name>
    <Value>True</Value>
  </property>
  <property>
    <Name>Another Boolean</Name>
    <Value>True</Value>
  </property>
  <property>
    <Name>Integer</Name>
    <Value>0</Value>
  </property>
</root>

So, how can I replace the singleton [2] in CF.value('(/root/property/Value)[2]', 'varchar(32)') with something that returns the node position?

I also recognize that there are likely a number of more elegant solutions, and am open to any of those as well.

Upvotes: 1

Views: 50

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138990

Skip the case statement and put the predicate on property name in the xPath expression where you get the value.

SQL Fiddle

MS SQL Server 2008 Schema Setup:

create table T(ItemID int identity primary key, CF xml);

insert into T(CF) values('<root>
  <property>
    <Name>Boolean</Name>
    <Value>True</Value>
  </property>
  <property>
    <Name>Integer</Name>
    <Value>0</Value>
  </property>
</root>');

insert into T(CF) values('<root>
  <property>
    <Name>Boolean</Name>
    <Value>True</Value>
  </property>
  <property>
    <Name>Another Boolean</Name>
    <Value>True</Value>
  </property>
  <property>
    <Name>Integer</Name>
    <Value>0</Value>
  </property>
</root>');

Query 1:

select T.ItemID,
       T.CF.value('(/root/property[Name/text() = "Boolean"]/Value/text())[1]', 'varchar(32)') as Boolean,
       T.CF.value('(/root/property[Name/text() = "Integer"]/Value/text())[1]', 'varchar(32)') as Integer
from T

Results:

| ItemID | Boolean | Integer |
|--------|---------|---------|
|      1 |    True |       0 |
|      2 |    True |       0 |

Upvotes: 1

Related Questions