Insert complex XML into SQL Server table

Question

Given this XML:

<Documents>
  <Batch BatchID="1" BatchName="Fred Flintstone">
    <DocCollection>
      <Document DocumentID="269" FileName="CoverPageInstructions.xsl">
        <MergeFields>
          <MergeField FieldName="A" Value="" />
          <MergeField FieldName="B" Value="" />
          <MergeField FieldName="C" Value="" />
          <MergeField FieldName="D" Value="" />
        </MergeFields>
      </Document>
      <Document DocumentID="6" FileName="USform8802.pdf">
        <MergeFields>
          <MergeField FieldName="E" Value="" />
          <MergeField FieldName="F" Value="" />
          <MergeField FieldName="G" Value="" />
          <MergeField FieldName="H" Value="" />
          <MergeField FieldName="I" Value="" />
        </MergeFields>
      </Document>
      <Document DocumentID="299" FileName="POASIDE.TIF">
        <MergeFields />
      </Document>
    </DocCollection>
  </Batch>
  <Batch BatchID="2" BatchName="Barney Rubble">
    <DocCollection>
      <Document DocumentID="269" FileName="CoverPageInstructions.xsl">
        <MergeFields>
          <MergeField FieldName="A" Value="" />
          <MergeField FieldName="B" Value="" />
          <MergeField FieldName="C" Value="" />
          <MergeField FieldName="D" Value="" />
        </MergeFields>
      </Document>
      <Document DocumentID="6" FileName="USform8802.pdf">
        <MergeFields>
          <MergeField FieldName="E" Value="" />
          <MergeField FieldName="F" Value="" />
          <MergeField FieldName="G" Value="" />
          <MergeField FieldName="H" Value="" />
          <MergeField FieldName="I" Value="" />
        </MergeFields>
      </Document>
    </DocCollection>
  </Batch>
</Documents>

I'm trying to achieve this result:

BatchID BatchName DocumentID FieldName
1   Fred Flintstone 269 A
1   Fred Flintstone 269 B
1   Fred Flintstone 269 C
1   Fred Flintstone 269 D
1   Fred Flintstone 6   E
1   Fred Flintstone 6   F
1   Fred Flintstone 6   G
1   Fred Flintstone 6   H
1   Fred Flintstone 6   I
1   Fred Flintstone 299 Null
2   Barney Rubble   269 A
2   Barney Rubble   269 B
2   Barney Rubble   269 C
2   Barney Rubble   269 D
2   Barney Rubble   6   E
2   Barney Rubble   6   F
2   Barney Rubble   6   G
2   Barney Rubble   6   H
2   Barney Rubble   6   I

I seems to be getting a Cartesian JOIN with this (each document has the full list of MergeField across all documents:

SELECT lvl1.n.value('@BatchID','int'), 
       lvl1.n.value('@BatchName','varchar(50)'), 
       lvl2.n.value('@DocumentID','int'), 
       lvl3.n.value('@FieldName','varchar(50)') 
FROM  @Data.nodes('Documents/*') lvl1(n)
CROSS APPLY lvl1.n.nodes('DocCollection/Document') lvl2(n)
CROSS APPLY lvl1.n.nodes('DocCollection/Document/MergeFields/MergeField') lvl3(n)

And am wondering how to get the result I need, with a Null value for the 299 DocumentID in Batch 1 that has no MergeField elements.

Any help is appreciated.

Thanks

Carl

UPDATE: Here's how to do the same using OpenXML:

 SELECT   
   BatchID,   
   BatchName,   
   DocumentID,   
   FileName,   
   KeyData,   
   FieldName  
   FROM OPENXML(@hdoc, '/Documents/Batch/DocCollection/Document/MergeFields/MergeField', 11)  
      WITH (BatchID varchar(100) '../../../../@BatchID',  
            BatchName varchar(100) '../../../../@BatchName',   
            DocumentID varchar(100) '../../@DocumentID',  
            FileName varchar(100) '../../@FileName',   
            KeyData varchar(100) '../../@KeyData',   
            FieldName varchar(100) '@FieldName');

John Cappelletti · Accepted Answer · 2017-07-17 23:49:31Z

3

Edit - Sorry I missed the NULL on Fred. Just changed the Cross Apply to Outer Apply.. The other Cross Apply could be an Outer Apply if desired

Notice Lvl3

Not clear why you are not creating an alias for each field. For example, I would expect something like BatchID = lvl1.n.value('@BatchID','int'),

SELECT lvl1.n.value('@BatchID','int'), 
       lvl1.n.value('@BatchName','varchar(50)'),
       lvl2.n.value('@DocumentID','int'),
       lvl3.n.value('@FieldName','varchar(50)') 
FROM  @Data.nodes('Documents/Batch') lvl1(n)
CROSS APPLY lvl1.n.nodes('DocCollection/Document') lvl2(n)
Outer APPLY lvl2.n.nodes('MergeFields/MergeField') lvl3(n)

Returns

edited Jul 17, 2017 at 23:49

answered Jul 17, 2017 at 23:38

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

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

2 Comments

CarlGanz Over a year ago

Thank you (again) John. This is perfect. I can't believe the troubles I've been having with the XML for this project.

John Cappelletti Over a year ago

@CarlGanz Happy to help. I too was a late user of XML. I watch snugo, he's the xml Ninja

Collectives™ on Stack Overflow

Insert complex XML into SQL Server table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related