CODEWITHSUNDEEP
sql-servert-sqlxpath
user1266921
user1266921

Reputation: 225

Xpath query on name rather than index

I have the following xml in an xml column in sql server, it is in a table along with a id column - 

<ArrayOfExtVar xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <ExtVar>
    <Name xsi:type="xsd:string">Rate Code</Name>
    <Value xsi:type="xsd:string">E46</Value>
  </ExtVar>
  <ExtVar>
    <Name xsi:type="xsd:string">Middle Name</Name>
    <Value xsi:type="xsd:string">Henry</Value>
  </ExtVar>
</ArrayOfExtVar>

And I have the following XPath -

SELECT CustID, 
ExtVars.value('(/ArrayOfExtVar/ExtVar/Value)[1]', 'Nvarchar(max)') AS RateCode,
ExtVars.value('(/ArrayOfExtVar/ExtVar/Value)[2]', ''Nvarchar(max)') AS MiddleInitial 
FROM dbo.Customer

which is great but what I'd really like to do is query the xml by 'Name' rather than the index ([1]) as these may be stored in different orders from time to time.

Basically what I need to know is how can I query by the value of 'Name' something like -

'(/ArrayOfExtVar/ExtVar/Value)[Rate Code]'

Could I add an attribute to the ExtVars Node and query on that instead?

Upvotes: 1

Views: 455

Answers (4)

Jens Erat
Jens Erat

Reputation: 38662

You can do axis steps in predicates, too.

//ExtVar[Name eq 'Rate Code']/Value

Upvotes: 0

Ian Roberts
Ian Roberts

Reputation: 122364

It sounds like you want

/ArrayOfExtVar/ExtVar[Name = 'Rate Code']/Value

Upvotes: 1

Cristian Lupascu
Cristian Lupascu

Reputation: 40516

I don't know if SQL Server's implementation of XPath supports the parent (..) operator, but you could try this instead:

/ArrayOfExtVar/ExtVar/Name[text()='Rate Code']/../Value

This finds the Name node that has an inner text of 'Rate Code', then navigates to its parent and from there it goes to the Value node underneath it.

If you could add Name as an attribute of the ExtVar node, that would make it a bit easier:

Sample XML:

...
<ExtVar name="Rate Code">
    <Value xsi:type="xsd:string">E46</Value>
</ExtVar>
...

XPath:

/ArrayOfExtVar/ExtVar[@name='Rate Code']/Value

Upvotes: 0

marc_s
marc_s

Reputation: 754268

Are you trying to do something like this:

DECLARE @input TABLE (ID INT, ExtVars XML)

INSERT INTO @input VALUES(1, '<ArrayOfExtVar xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <ExtVar>
    <Name xsi:type="xsd:string">Rate Code</Name>
    <Value xsi:type="xsd:string">E46</Value>
  </ExtVar>
  <ExtVar>
    <Name xsi:type="xsd:string">Middle Name</Name>
    <Value xsi:type="xsd:string">Henry</Value>
  </ExtVar>
</ArrayOfExtVar>')

SELECT
    Name = ExtVar.value('(Name)[1]', 'varchar(50)'),
    [Value] = ExtVar.value('(Value)[1]', 'varchar(50)')
FROM
    @input
CROSS APPLY
    ExtVars.nodes('/ArrayOfExtVar/ExtVar') AS XTbl(ExtVar)
WHERE   
    ID = 1

Returns output like this:

enter image description here

And of course, you can add a name-based WHERE clause to this (that "reaches" into the XML to determine the result), too:

SELECT
    Name = ExtVar.value('(Name)[1]', 'varchar(50)'),
    [Value] = ExtVar.value('(Value)[1]', 'varchar(50)')
FROM
    @input
CROSS APPLY
    ExtVars.nodes('/ArrayOfExtVar/ExtVar') AS XTbl(ExtVar)
WHERE   
    ExtVar.value('(Name)[1]', 'varchar(50)') LIKE 'Rate%'   -- or whatever you want

Upvotes: 0

Related Questions