XQUERY - How to use the sql:variable in 'value()' function?

Question

The query below is trying to select a child node of a given Node. How do I use a variable instead of hard coding the child node such that I can pass them as parameters in a SProc?

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">
      <children>
      <DisplayName type="string" display="Display name">GMT Standard Time</DisplayName>
      <HiddenName type="string" display="Hidden name">GMT</HiddenName>
      </children>
    </TimeZone>
  </Elem1>
</Root>') 

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

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

Mikael Eriksson · Accepted Answer · 2011-08-11T13:55:12.783

5

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">
    <DisplayName type="string" display="Display name">No this</DisplayName>
    <Name type="string" display="First name">John</Name>
    <TimeZone display="Time zone">
      <children>
        <DisplayName type="string" display="Display name">GMT Standard Time</DisplayName>
        <HiddenName type="string" display="Hidden name">GMT</HiddenName>
      </children>
    </TimeZone>
  </Elem1>
</Root>') 

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

declare @Node2 varchar(50)
set @Node2 = 'DisplayName'

select N2.Value.value('.', 'varchar(100)') as Value 
from @T as T
  cross apply (select T.XMLCol.query('//*[local-name()=sql:variable("@Node1")]')) as N1(Value) 
  cross apply (select N1.Value.query('//*[local-name()=sql:variable("@Node2")]')) as N2(Value)

edited Aug 11 '11 at 13:55

answered Aug 11 '11 at 11:25

Mikael Eriksson

136,425
22
210
281

Try again please. It's hard to type on the phone :). – Mikael Eriksson Aug 11 '11 at 11:58
Although this works, but still how do I replace the entire clause with a variable - '(//TimeZone/children/*[local-name()=sql:variable("@NodeX")])[1]' – Angshuman Agarwal Aug 11 '11 at 12:04
You don't need the entire clause. The double slashes '//' in the beginning does a deep search for the node. It will find the first node in the sub-tree with the specified name. – Mikael Eriksson Aug 11 '11 at 12:10
Ok. But if one more 'SystemName' node is present before under a different Node Element Tag, then that will be encountered first if I use //Systemname directly. How do I filter to say search only under TimeZone node i.e. @Node variable ? – Angshuman Agarwal Aug 11 '11 at 12:43
It is the timezone node that you search with '@NodeX' since you have specified that in the cross apply using '@Node'. – Mikael Eriksson Aug 11 '11 at 13:03
select N.value('(//*[local-name()=sql:variable("@NodeX")])[1]', 'varchar(100)') as Value from @T as T cross apply T.XMLCol.nodes('//*[local-name()=sql:variable("@Node")]') as X(N) - Does not select the one in TimeZone, rather selects the one 1st encountered – Angshuman Agarwal Aug 11 '11 at 13:20
@SeeSharp - Back at a computer now. Updated answer. – Mikael Eriksson Aug 11 '11 at 13:55

score 1 · Answer 2 · answered Aug 11 '11 at 11:56

1

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

;WITH XMLNAMESPACES(DEFAULT 'http://tempuri.org')
select N.value('.', 'varchar(100)') as Value
from @T as T
  cross apply T.XMLCol.nodes('//*[local-name()=sql:variable("@Node")]/children/*[local-name(.)=sql:variable("@ChildName")]') as X(N)

answered Aug 11 '11 at 11:56

Dalex

3,585
19
25

Can we pass nodes using variables instead of '//*[local-name()=sql:variable("@Node")' ? See - http://stackoverflow.com/questions/7038038/xquery-sql-how-to-pass-individual-node-elements-as-variables – Angshuman Agarwal Aug 12 '11 at 09:35

XQUERY - How to use the sql:variable in 'value()' function?

2 Answers2

Linked