CarlGanz
CarlGanz

Reputation: 197

Insert complex XML into SQL Server table

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');  

Upvotes: 3

Views: 435

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 82020

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

enter image description here

Upvotes: 3

Related Questions