xml - 如何将 XML 列连接回它的来源记录？

Question

我在 SQL Server 2005 的数据库中有一个表“Blah”，其中包含一个 PK 列 BlahID 和一个 XML 列 BlahItems。

这个表有如下记录...

BlahID BlahItems------ ------------------------------------------------------1      <root><item name="Fred" /><item name="Wilma" /></root>2      <root><item name="Lisa" /><item name="Bart" /></root>

How can I query that table to produce the following....

BlahID BlahItem------ --------1      Fred1      Wilma2      Lisa2      Bart

The closest I've managed to get is on a per record FUNCTION that does something along the lines of the following...

CREATE FUNCTION dbo.Blahs(@id int)
RETURNS @list TABLE (BlahID int, BlahItem nvarchar(max))
BEGIN
    DECLARE @xml AS xml
    SELECT @xml = BlahItems FROM dbo.Blah AS b WHERE b.BlahID = @id

    INSERT INTO @list
    SELECT @id, tbl.col.value('@name','nvarchar(max)')
    FROM @xml.nodes('/root/item') tbl(col)
    RETURN
END

SELECT * FROM dbo.Blahs(1)

BlahID BlahItem------ --------1      Fred1      Wilma

我的最终目标是创建“扩展”数据的 View ，然后对该 View 进行查询。

Answer 1

你可以试试这个查询:

SELECT BlahID, XmlItems.BlahItem.value('@name', 'nvarchar(100)') AS BlahItem
FROM Blah CROSS APPLY BlahItems.nodes('/root/item') AS XmlItems(BlahItem)

更多信息请查看this three-part article亚历克斯荷马。