鉴于此
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>
我需要以这种格式将它插入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教程吗？
谢谢
卡尔
解决方法
你很亲密
将别名更改为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
