SQL Parse an xml string

Question

I have a bunch of XMLs that I need to parse with SQL.

The XML can take on multiple forms:

<Grandparent>
    <parent>
      <child1>something</child1>
      <child2>something</child2>
    </parent>
</Grandparent>

or

<Grandparent>
      <child1>something</child1>
      <child2>something</child2>
</Grandparent>

Additionally, the number of "child" nodes is variable and there is no way of knowing before hand how many children there are.

What i have done so far is:

@xml.nodes('/Grandparent')

which returns either the <parent> node and children or simply the child nodes depending on the format of the xml.

The version of SQL and the fact that i'm writing it as an SQL function seems to mean that trying to get valueas shown in this anwser does not work.

Therefore, I decided to parse the string. Essentially, I look for < and take the substring from there until > for the node name. Then I take anything between > and </ for the value. I do so in a while loop until the xml string is finished. It works perfectly unless the xml has that parent node.

I don't know how to determine whether that parent node is there and how to ignore it if it is. This is where I am stuck.

What I want to get in either case is:

Node   | Value
child1 | something
child2 | something

etc for as many child nodes that there is.

Still not clear that if you can do @xml.nodes('/Grandparent'), why then you can't get value as shown in that answer link? And given that 2 samples what is the output you expect to get? — har07
– har07, Commented Apr 27, 2015 at 2:45

Stuart Ainsworth · Accepted Answer · 2015-04-27 02:53:58Z

1

Going out on a limb here with two assumptions; your question isn't clear about the following:

I'm assuming that your child nodes have the same name (e.g., child, not child1 and child2), and
You want a SQL statement that returns 1 child per row.

If either of those assumptions is incorrect, this answer won't help :)

DECLARE @xml XML = '<Grandparent>
    <parent>
      <child>something</child>
      <child>something</child>
    </parent>
</Grandparent>'


SELECT x.value('.[1]', 'varchar(100)') 
FROM @xml.nodes('/Grandparent//child') t(x)


SET @xml= '<Grandparent>
      <child>something</child>
      <child>something</child>
</Grandparent>'


SELECT x.value('.[1]', 'varchar(100)') 
FROM @xml.nodes('/Grandparent//child') t(x)

answered Apr 27, 2015 at 2:53

Stuart Ainsworth

12.9k44 silver badges46 bronze badges

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

Comments

har07 · Accepted Answer · 2015-04-27 03:31:48Z

0

You can use descendant axis // to get child nodes at any level depth within a parent node.

Another useful xpath syntax for this task is local-name() which return current context node/attribute's name without namespace :

select c.value('local-name(.)', 'varchar(max)') as 'node'
        , c.value('.', 'varchar(max)') as 'value'
from @xml.nodes('/Grandparent//*[not(*)]') as T(c)

This xpath bit //*[not(*)] means select descendant nodes that doesn't have child node, in other words select the inner most descendant.

SQL Fiddle

answered Apr 27, 2015 at 3:31

har07

89.5k12 gold badges87 silver badges143 bronze badges

1 Comment

mo alaz Over a year ago

'/Grandparent//*[not(*)]' is all i needed! Thank you!

Dan Guzman · Accepted Answer · 2015-04-27 03:55:03Z

0

Try:

DECLARE @xml xml = N'
    <Grandparent>
        <parent>
          <child1>something</child1>
          <child2>something</child2>
        </parent>
    </Grandparent>';

SELECT
     child.value('fn:local-name(.)', 'varchar(100)') AS Node
    ,child.value('.', 'varchar(100)') AS value
FROM @xml.nodes('//*[self::child1 or self::child2]') AS ansestor(child);

SET @xml = N'
    <Grandparent>
          <child1>something</child1>
          <child2>something</child2>
    </Grandparent>';

SELECT
     child.value('fn:local-name(.)', 'varchar(100)') AS Node
    ,child.value('.', 'varchar(100)') AS value
FROM @xml.nodes('//*[self::child1 or self::child2]') AS ansestor(child);

answered Apr 27, 2015 at 3:55

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

Collectives™ on Stack Overflow

SQL Parse an xml string

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related