插入XML到SQL Server表
问题描述:
鉴于这种XML:插入XML到SQL Server表
<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>
我需要将其插入表中的SQL Server格式为:
BatchID BatchName DocumentID
1 Fred Flintstone 269
1 Fred Flintstone 6
1 Fred Flintstone 299
2 Barney Rubble 269
2 Barney Rubble 6
这个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)
给我这个结果:
BatchID BatchName DocumentID
1 Fred Flintstone NULL
2 Barney Rubble NULL
我在做什么错?
另外,有人可以推荐一个很好的SQL Server中的XML教程?
感谢
卡尔
答
你接近。
使用通配符和CROSS APPLY,可以生成多个记录。
更改别名为lvl1和lvl2以更好地说明。
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)
返回
BatchID BatchName DocumentID
1 Fred Flintstone 269
1 Fred Flintstone 6
1 Fred Flintstone 299
2 Barney Rubble 269
2 Barney Rubble 6
好的解决方案,从我身边+1,但是为什么''第一'.nodes()'? – Shnugo
@Shnugo现在你提到它/ Batch会更安全/更具体。还是你在想别的东西? –
不,只是为了避免错误,如果可能有更多的节点与其他名称... – Shnugo