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

Answer 1

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