CODEWITHSUNDEEP
sql-serverxml
Rahul Chaudhari
Rahul Chaudhari

Reputation: 148

Extract xml tag value from XML column in SQL Server

I am working with xml datatype columns in SQL Server. I know how to extract the values from XML columns by using query() or value() functions but my problem is slightly different.

In my database table the XML has no fixed pattern. This means, the XML tags are not fixed and varying in most of the records.

As an example:

<Types>
  <request>
    <Rollcount>34</Rollcount>
  </request>
</Types>

<Types>
  <general>
    <Rollcount>5</Rollcount>
  </general>
</Types>

<Types>
  <land>
     <Rollcount>21</Rollcount>
  <land>
</Types>

In above example you can see, XML has different tags like <request>, <general> and <land>. There is no fixed pattern.

Now I want to extract a value of <Rollcount>. Could you please tell me how can I do this?

Upvotes: 0

Views: 1454

Answers (1)

Thom A
Thom A

Reputation: 95554

You could wildcards. If we assume that the top nodes is always Types, and Rollcount is always the 3rd level then you could do this:

DECLARE @XML xml ='<Types>
  <general>
    <Rollcount>5</Rollcount>
  </general>
</Types>';

SELECT X.RC.value('.','int')
FROM @XML.nodes('Types/*/Rollcount')X(RC);

db<>fiddle

Upvotes: 1

Related Questions