CODEWITHSUNDEEP
xmlsql-server-2012xquery
Jesper Lund Stocholm
Jesper Lund Stocholm

Reputation: 2013

Querying XML data (attribute) in Sql Server

I have some XML in an Xml-column in Sql Server that I would like to provide a view on top on. Specifically, I would like to expose the "version" attribute of the root element alongside the entire XML-chunk itself.

The table has a single column named "Tree".

The XML looks like this:

<?xml version="1.0" encoding="utf-8" ?>
<ClassificationTree xmlns="http://schemas.tempuri.org/2014/02/17" version="1.0">
  <Classification name="WTG" priority="1">
    <Classification name="Corrective" priority="1">
      <Classification name="RDSPP-Codes" priority="1" />
    </Classification>
    <Classification name="Predetermined" priority="2">
      <Classification name="Non Service related (tests, visits etc)" priority="1" />
      <Classification name="Scheduled Service" priority="2" />
    </Classification>
  </Classification>
</ClassificationTree>

When I query it like this:

WITH XMLNAMESPACES ('http://schemas.tempuri.org/2014/02/17' AS ns1)
select 
  Tree.value('(/ns1:ClassificationTree/@version)[0]','varchar(20)') as Version
  , Tree 
from 
  ClassificationTree

I get the Xml-chunk all right, but the "version"-column is NULL.

What am I missing here?

Upvotes: 0

Views: 137

Answers (1)

adamretter
adamretter

Reputation: 3517

Indexes in XQuery start at 1 and not 0. I would suggest first trying to replace from within your query:

(/ns1:ClassificationTree/@version)[0]

to:

(/ns1:ClassificationTree/@version)[1]

However what you probably meant was -

/ns1:ClassificationTree[1]/@version

As you want to take the version attribute from the first ClassificationTree. What you were doing is retrieving all versions of all ClassificationTrees and then selecting the first (or you would have been if you used [1] and not [0]) which is much less efficient.

Upvotes: 2

Related Questions