Reputation: 197
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
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
Upvotes: 3