将XML插入SQL Server表

发布时间：2020-12-16 23:01:37 所属栏目：百科来源：网络整理

导读：鉴于此 XML： Documents Batch BatchID = "1" BatchName = "Fred Flintstone" DocCollection Document DocumentID = "269" KeyData = "" / Document DocumentID = "6" KeyData = "" / Document DocumentID = "299" KeyData = "" ImageFile="Test.TIF" / /Doc

鉴于此 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教程吗？

谢谢

卡尔

解决方法

你很亲密

使用通配符和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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!