将XML插入SQL Server表中

Question

将XML插入SQL Server表中

4

鉴于这个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的好教程？

谢谢。

卡尔

- CarlGanz

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- John Cappelletti · Accepted Answer

你离答案很接近。

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