Inserting XML into SQL Server table

Question

Given this XML:

<Documents>
    <Batch BatchID = "1" BatchName = "Fred Flintstone">
        <DocCollection>
            <Document DocumentID = "269" KeyData = "" />
            <Document DocumentID = "6"   KeyData = "" />
            <Document DocumentID = "299" KeyData = ""     ImageFile="Test.TIF" />
        </DocCollection>    
    </Batch>    
    <Batch BatchID = "2" BatchName = "Barney Rubble">
        <DocCollection>
            <Document DocumentID = "269" KeyData = "" />
            <Document DocumentID = "6"   KeyData = "" />
        </DocCollection>
    </Batch>
</Documents>

I need to insert it into a table in SQL Server in this format:

BatchID   BatchName           DocumentID
1         Fred Flintstone     269
1         Fred Flintstone     6
1         Fred Flintstone     299
2         Barney Rubble       269
2         Barney Rubble       6

This SQL:

   SELECT
        XTbl.XCol.value('./@BatchID','int') AS BatchID,
        XTbl.XCol.value('./@BatchName','varchar(100)') AS BatchName,
        XTbl.XCol.value('DocCollection[1]/DocumentID[1]','int') AS DocumentID
   FROM @Data.nodes('/Documents/Batch') AS XTbl(XCol)

gets me this result:

BatchID BatchName       DocumentID
1       Fred Flintstone NULL
2       Barney Rubble   NULL

What am I doing wrong?

Also, can someone recommend a good tutorial for XML in SQL Server?

Thanks

Carl

John Cappelletti · Accepted Answer · 2017-07-14 21:15:22Z

7

You were close.

Using a wildcard and a CROSS APPLY, you can generate multiple records.

Changed alias to lvl1 and lvl2 to better illustrate.

Declare @XML xml = '
<Documents>
    <Batch BatchID = "1" BatchName = "Fred Flintstone">
        <DocCollection>
            <Document DocumentID = "269" KeyData = "" />
            <Document DocumentID = "6"   KeyData = "" />
            <Document DocumentID = "299" KeyData = ""     ImageFile="Test.TIF" />
        </DocCollection>    
    </Batch>    
    <Batch BatchID = "2" BatchName = "Barney Rubble">
        <DocCollection>
            <Document DocumentID = "269" KeyData = "" />
            <Document DocumentID = "6"   KeyData = "" />
        </DocCollection>
    </Batch>
</Documents>
'

Select BatchID    = lvl1.n.value('@BatchID','int') 
      ,BatchName  = lvl1.n.value('@BatchName','varchar(50)') 
      ,DocumentID = lvl2.n.value('@DocumentID','int') 
 From  @XML.nodes('Documents/Batch') lvl1(n)
 Cross Apply lvl1.n.nodes('DocCollection/Document') lvl2(n)

Returns

BatchID BatchName       DocumentID
1       Fred Flintstone 269
1       Fred Flintstone 6
1       Fred Flintstone 299
2       Barney Rubble   269
2       Barney Rubble   6

edited Jul 14, 2017 at 21:15

answered Jul 14, 2017 at 13:14

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

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

5 Comments

Gottfried Lesigang Over a year ago

Good solution, +1 from my side, but why the * in the first .nodes()?

John Cappelletti Over a year ago

@Shnugo Now that you mention it, /Batch would be safer/more specific. Or were you thinking something else?

Gottfried Lesigang Over a year ago

No, just to avoid errors, if there might be more nodes with other names...

John Cappelletti Over a year ago

@Shnugo Updated, but the Cross Apply would still target DocCollection/Document. Just for fun I added <MiscOther><Document DocumentID = "269" KeyData = "" /></MiscOther> ... within a Batch and as a peer

Gottfried Lesigang Over a year ago

Well, this is sophisticated :-D Tried to enclose your test <Document> with <DocCollcetion>?

Collectives™ on Stack Overflow

Inserting XML into SQL Server table

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related