SQLXML - Search and Query node element?

Question

I have an XML like this stored in an XML datatype column (will have multiple such rows in table)-

<Root xmlns="http://tempuri.org" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Elem1 type="T1">
    <Name type="string" display="First name">John</Name>
    <TimeZone display="Time zone">
      <DisplayName type="string" display="Display name">GMT Standard Time</DisplayName>
    </TimeZone>
  </Elem1>
</Root>

How can I filter based on a node element say (using SQL SERVER 2008 R2) - get all 'Elem1' nodes or get all 'Name' nodes or get all TimeZone nodes ? Something like using local-name() function ?

EDIT - Part Solution -

I got the solution partly (see John's reply below and then run this) -

SELECT C1.query('fn:local-name(.)') AS Nodes FROM [dbo].[MyXmlTable] AS MyXML CROSS APPLY MyXML.MyXmlCol.nodes('//*') AS T ( C1 )

The query above returns all the node elements across the TABLE. Now, I want to say filter upon specific elements and return the element and its value or its attribute value. How to achieve this (by using WHERE clause or any other filter mechanism)?

Mikael Eriksson · Accepted Answer · 2011-08-09T15:54:53.497

1

I'm not sure what result you are looking for but something like this perhaps.

declare @T table(XMLCol xml)
insert into @T values
('<Root xmlns="http://tempuri.org" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Elem1 type="T1">
    <Name type="string" display="First name">John</Name>
    <TimeZone display="Time zone">
      <DisplayName type="string" display="Display name">GMT Standard Time</DisplayName>
    </TimeZone>
  </Elem1>
</Root>') 

declare @Node varchar(50)
set @Node = 'Elem1'

select N.query('.') as Value
from @T as T
  cross apply T.XMLCol.nodes('//*[local-name()=sql:variable("@Node")]') as X(N)

Result:

<p1:Elem1 xmlns:p1="http://tempuri.org" type="T1">
  <p1:Name type="string" display="First name">John</p1:Name>
  <p1:TimeZone display="Time zone">
    <p1:DisplayName type="string" display="Display name">GMT Standard Time</p1:DisplayName>
  </p1:TimeZone>
</p1:Elem1>

Edit

If you want the actual value instead of the entire XML you can do like this instead.

declare @Node varchar(50)
set @Node = 'TimeZone'

select N.value('.', 'varchar(100)') as Value
from @T as T
  cross apply T.XMLCol.nodes('//*[local-name()=sql:variable("@Node")]') as X(N)

Result:

Value
------------------
GMT Standard Time

edited Aug 09 '11 at 15:54

answered Aug 09 '11 at 12:02

Mikael Eriksson

136,425
22
210
281

The result should the value 'GMT Standard Time' only and input will be 'TimeZone' node element only – Angshuman Agarwal Aug 09 '11 at 15:46
@SeeSharp - Updated answer. If you always want to query "TimeZone" you just have to replace `sql:variable("@Node")` with `"TimeZone"`. – Mikael Eriksson Aug 09 '11 at 15:56
How do I use the sql:variable in 'value()' xml function too ? Say, if I do not want to pass the current node, instead I want to pass 'DisplayName' node. – Angshuman Agarwal Aug 11 '11 at 09:32
@SeeSharp - Well... depends on what you want to do. I suggest you add that as a new question with some sample XML data and the **expected output**. – Mikael Eriksson Aug 11 '11 at 09:52
See here - http://stackoverflow.com/questions/7024519/xquery-how-to-use-the-sqlvariable-in-value-function – Angshuman Agarwal Aug 11 '11 at 10:46

score 1 · Answer 2 · answered Aug 10 '11 at 10:12

You can transform XML into table like here:

  declare @XML xml='<Root xmlns="http://tempuri.org" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Elem1 type="T1">
    <Name type="string" display="First name">John</Name>
    <TimeZone display="Time zone">
      <DisplayName type="string" display="Display name">GMT Standard Time</DisplayName>
    </TimeZone>
  </Elem1>
</Root> '

;WITH XMLNAMESPACES(DEFAULT 'http://tempuri.org'),
numbers as(
SELECT  ROW_NUMBER() OVER(ORDER BY o1.object_id,o2.object_id) Num
FROM    sys.objects o1 CROSS JOIN  sys.objects o2),
 c as(
SELECT 
b.value('local-name(.)','nvarchar(1000)') Node_Name,
b.value('./text()[1]','nvarchar(1000)') Node_Value,
b.value('count(@*)','nvarchar(MAX)') AttributeCount,
Num Attribute_Number
FROM 
@xml.nodes('Root//*') a(b)
CROSS APPLY Numbers
WHERE Num<=b.value('count(@*)','nvarchar(MAX)')
)
SELECT c.Node_Name,c.node_Value,Attribute_Number,
    @XML.query('for $Attr in //*/.[local-name(.)=sql:column("Node_Name")]/@*[sql:column("Attribute_Number")] return local-name($Attr)').value('.','nvarchar(MAX)') Attribute_Name,
    @XML.value('data(//*/.[local-name(.)=sql:column("Node_Name")]/@*[sql:column("Attribute_Number")])[1]','nvarchar(1000)') Attribute_Value
FROM    c

Result:

Node_Name   node_Value          Attribute_Number    Attribute_Name  Attribute_Value
Elem1        NULL                       1              type             T1
Name         John                       1              type           string
Name         John                       2              display        First name
TimeZone     NULL                       1              display        Time zone
DisplayName GMT Standard Time           1              type            string
DisplayName GMT Standard Time           2              display     Display name

Later you can query this result to get node/attribute value which do you need.

But it works only in your example, when you have only one node and all names are unique. In multinode XML you should use hierarchical numbering like '1-1-2' or something like this. It is much more complicated and i do not suggest to going this way.

JohnD · Answer 3 · 2011-08-09T11:34:00.473

It's not clear to me exactly what your output should look like. However, this should get you started:

create table MyXmlTable (MyXmlCol xml)
insert into MyXmlTable (MyXmlCol) values 
(
'
<Root xmlns="http://tempuri.org" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Elem1 type="T1">
    <Name type="string" display="First name">John</Name>
    <TimeZone display="Time zone">
      <DisplayName type="string" display="Display name">GMT Standard Time</DisplayName>
    </TimeZone>
  </Elem1>
  <Elem1 type="T2">
    <Name type="string" display="First name">Fred</Name>
    <TimeZone display="Time zone">
      <DisplayName type="string" display="Display name">EST Standard Time</DisplayName>
    </TimeZone>
  </Elem1>
</Root> 
');

;WITH XMLNAMESPACES(DEFAULT 'http://tempuri.org')
select MyXmlCol.query('/Root/Elem1/Name')
from MyXmlTable

This queries the XML for the "Name" elements -- you can modify the query depending on what kind of output you want exactly. It's a bit long, but the MSDN article on SQLXML is pretty informative:

http://msdn.microsoft.com/en-us/library/ms345117(v=sql.90).aspx

Hope this helps!

John

Update: you can add a where clause something like this. I'm still not clear on what you want the output to look like, but this will filter out the "Elem1" values:

SELECT C1.query('fn:local-name(.)') AS Nodes 
FROM [dbo].[MyXmlTable] AS MyXML 
CROSS APPLY MyXML.MyXmlCol.nodes('//*') AS T ( C1 ) 
WHERE CAST(C1.query('fn:local-name(.)') AS NVARCHAR(32)) <> 'Elem1'

One more update; hopefully this is the answer you are looking for!

Try using a wildcard in the query. I had to use dynamic SQL because the XML query() function will only take string literals for paths (you can use sql:variable("@filter") for values, but I wasn't able to get that working for a path.)

DECLARE @filter nvarchar(20)
SET @filter = '*/Elem1'

DECLARE @sqlCommand nvarchar(1000)
SET @sqlCommand = 
    ';WITH XMLNAMESPACES(DEFAULT ''http://tempuri.org'')
    select MyXmlCol.query(''' + @filter + ''')
    from MyXmlTable'
print @sqlCommand
EXECUTE sp_executesql @sqlCommand, N'@filter nvarchar(20)', @filter = @filter

This will return the Elem1 XML (and all sub-nodes):

<p1:Elem1 xmlns:p1="http://tempuri.org" type="T1">
  <p1:Name type="string" display="First name">John</p1:Name>
  <p1:TimeZone display="Time zone">
    <p1:DisplayName type="string" display="Display name">GMT Standard Time</p1:DisplayName>
  </p1:TimeZone>
</p1:Elem1>
<p2:Elem1 xmlns:p2="http://tempuri.org" type="T2">
  <p2:Name type="string" display="First name">Fred</p2:Name>
  <p2:TimeZone display="Time zone">
    <p2:DisplayName type="string" display="Display name">EST Standard Time</p2:DisplayName>
  </p2:TimeZone>
</p2:Elem1>

And if you want to pick out "TimeZone" you would do this:

SET @filter = '*/*/TimeZone'

That's useful. Thanks. But, the user can give any node element as an input variable say 'Elem1' and based on that I have to filter out that node element and its corresponding value. I do not know where does Elem1 or rather any element lies in the XML hierarchy. If node element is not found then return error. NOTE that: Elem1 or Name elements can be part of the XML column across multiple rows in the table — Angshuman Agarwal, Aug 02 '11 at 13:15
I saw your update and I'm still a little unclear what you want the output to look like. Could you just modify your query to remove certain results? Let me post an update to my answer. — JohnD, Aug 02 '11 at 18:51
The Input will be any Node Element and the Output should be the corresponding Node Element and its value (all in T-SQL because this should be stored as part of a View). Hope I am clear. — Angshuman Agarwal, Aug 03 '11 at 08:17

SQLXML - Search and Query node element?

3 Answers3