Extract xml tag value from XML column in SQL Server

Question

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?

Thom A · Accepted Answer · 2021-07-23 12:39:01Z

1

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

edited Jul 23, 2021 at 12:39

answered Jul 23, 2021 at 12:19

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Extract xml tag value from XML column in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related