如何将 XML 列连接回它源自的记录? [英] How can I join an XML column back onto the record it originates from?
问题描述
我在 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>如何查询该表以生成以下内容....
<前>BlahID BlahItem------ ------1 弗雷德1 威尔玛2 丽莎2 巴特我设法获得的最接近的是每条记录的功能,它按照以下方式执行某些操作...
CREATE FUNCTION dbo.Blahs(@id int)返回 @list 表 (BlahID int, BlahItem nvarchar(max))开始声明 @xml 为 xmlSELECT @xml = BlahItems FROM dbo.Blah AS b WHERE b.BlahID = @id插入@listSELECT @id, tbl.col.value('@name','nvarchar(max)')FROM @xml.nodes('/root/item') tbl(col)返回结尾SELECT * FROM dbo.Blahs(1)
<前>BlahID BlahItem------ ------1 弗雷德1 威尔玛
我的最终目标是创建扩展"数据的 VIEW,然后针对该视图进行查询.
你可以试试这个查询:
SELECT BlahID, XmlItems.BlahItem.value('@name', 'nvarchar(100)') AS BlahItemFROM Blah CROSS APPLY BlahItems.nodes('/root/item') AS XmlItems(BlahItem)
有关详细信息,请查看 Alex Homer 的这篇由三部分组成的文章.
I have a table "Blah" with a PK column BlahID and an XML column BlahItems in a database on SQL Server 2005.
This table has records as follows...
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 Fred 1 Wilma 2 Lisa 2 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 Fred 1 Wilma
My ultimate goal is to create a VIEW of the "expanded" data and then query against the view.
You can try this query:
SELECT BlahID, XmlItems.BlahItem.value('@name', 'nvarchar(100)') AS BlahItem
FROM Blah CROSS APPLY BlahItems.nodes('/root/item') AS XmlItems(BlahItem)
For more info check this three-part article by Alex Homer.
这篇关于如何将 XML 列连接回它源自的记录?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!