Finding Unknown XML Grandchildren Using SQL

Question

Is there a way to search through XML Nodes using SQL to see if Parent Nodes have ChildNodes and Grandchildren? I'm trying to test some XML files to see if there are any unknown children:

<form>
  <ItemType>New Regular</ItemType>
  <FirstBuyDate>02/01/2007</FirstBuyDate>
  <FirstShipDate>02/05/2007</FirstShipDate>
  <ItemBrand>Gillette Blade/Razors</ItemBrand>
  <ItemDescription>...</ItemDescription>
  <Size>8.00</Size>
  <InnerOffInvoice />
  <MasterOffInvoice />
  <PalletItems>
    <ID>908402</ID> 
    <PalletShipDate>04/03/13</PalletShipDate>
  </PalletItems>
  <ReviewComments />
</form>

In this example, I know that PalletItems has children. So I am able to make tables, insert that data accordingly. But how can I be sure I've gotten all of the grandchildren of <form>? What if there are unknown children like with <ItemDescription> - how do I find the children I don't know about? How can I test all of these nodes to see if <form> has any grandchildren?

This code here showed me how many children had:

SELECT distinct Parent.Items.value('local-name(.)', 'varchar(100)') as 'Item'
    FROM    dbo.FormResults 
    CROSS APPLY xmlformfields.nodes('/form/*') as Parent(Items)

Do any of you have suggestions to find the grandchildren? Thanks!

Also as a side note - does anyone know how to have this SELECT statement put the children in the order they appear in the xml? Currently when the code grabs the children it will display them in the column in random order:

    Items
1. FirstBuyDate
2. Size
3. ItemType
4. PalletItems... etc.

I would like them to be displayed like this:

    Items:
1. ItemType
2. FirstBuyDate 
3. FirstShipDate
4. ItemBrand... etc.

---------------------UPDATE----------------------

Now that we know that (/form//*) will get the children and grandchild thanks to valex

How can I put the children in one column and the grandchildren in the next? Would it be something like this:

SELECT distinct Parent.Items.value('local-name(.)', 'varchar(100)') as 'Child of Form',
CASE when child.items.value('local-name(.)', 'varchar(100)') IS NULL then NULL ELSE 'Has Child' end as 'Grandchild of Form'
FROM    dbo.FormResults 
CROSS APPLY xmlformfields.nodes('/form/*')  as Parent(Items) 
Cross Apply parent.items.nodes('/form//*')  as child(items)

score 2 · Accepted Answer · edited May 23 '17 at 12:05

2

To get all nodes not only from the first level use /form//* with // instead of /form/*

SELECT distinct Parent.Items.value('local-name(.)', 'varchar(100)') as 'Item'
    FROM    dbo.FormResults 
    CROSS APPLY xmlformfields.nodes('/form//*') as Parent(Items)

SQLFiddle example

To get also parent nodes use syntax ../. in local-name() call. To get an Index of child inside a parent node and order by it you can use XQuery expression

for $i in . return count(../*[. << $i])

So the final query with order:

SELECT distinct 
          Parent.Items.value('local-name(.)', 'varchar(100)') as 'Item',
          Parent.Items.value('local-name(../.)', 'varchar(100)') as 'ParentItem',
          Parent.Items.value('for $i in . return count(../*[. << $i])','int') 
              as ChildIndex
    FROM    dbo.FormResults 
    CROSS APPLY xmlformfields.nodes('/form//*') as Parent(Items)
    ORDER BY ParentItem,ChildIndex

SQLFiddle example

edited May 23 '17 at 12:05

Community

1
1

answered Jun 06 '14 at 13:30

valex

23,966
7
43
60

That's awesome! It works! Thank you so much! Do you know how I could put the Children in one column and the grandchildren in another? (See my added code in my submission) Thanks so much! – user3281388 Jun 06 '14 at 13:44
@user3281388 Look at the second query in the answer. – valex Jun 06 '14 at 14:03
Thanks so much for your help! I really appreciate it! :) This works good. Do you by any chance know how to get the ('local-name') to select the elements in the order they are in xml? Since the first xml child is ItemType - I'd like that to first in my row. But my rows are random and not in the order they are in xml. Got any tips? – user3281388 Jun 06 '14 at 14:12
@user3281388 I've changed the second query so try to use it. – valex Jun 06 '14 at 14:29
It's taking quite a bit of time to execute... around 7 minutes and I'm still waiting... The other ones took around 3 minutes are less. Do you think it should take that long? – user3281388 Jun 06 '14 at 14:45
Just completed!!! Perfect!! I'm so excited! Thanks so much for your help!!! :) Have a great day! – user3281388 Jun 06 '14 at 14:48

Finding Unknown XML Grandchildren Using SQL

1 Answers1