从具有XML列的SQL表中检索属性和值 [英] Retrieve attributes and values from a SQL table which has a XML column

问题描述

我在DB中有一个SQL表，该表有一个包含XML数据的列.我需要为表的每一行解析XML数据列，然后将其批量插入到另一个表中.

我尝试使用SQL并编写了一个游标，该游标遍历表的每一行；在XML的属性值中查找字符串模式，然后返回结果集.它为我完成了工作，但是它很慢，幸运的是，只有很少的行要处理，因此它可以工作，但如果行数超过了可能会成问题的话.我想知道SSIS是否可以通过某种方式完成相同的工作

假设表名称为EquipmentChartGroupLayout，而具有XML数据的列为equipmentchartlayout

从VIMSVehicle_Func_Sprint.Admin.EquipmentChartGroupLayout
声明用于选择equipmentchartlayout.query(''(//carChart/item)'')的当前光标 声明@xmlCar xml

打开当前

从cur INTO中获取NEXT到@xmlCar
虽然(@@ FETCH_STATUS<> -1)
开始
IF(@@ FETCH_STATUS<> -2)
开始
SELECT DISTINCT
CAST(Attribute.Name.query(''local-name(.)'')AS VARCHAR(100))属性，
Cast(Attribute.Name.value(''.''，``VARCHAR(100)'')AS VARCHAR(100))值
FROM @ xmlCar.nodes(''/item/@ *'')属性(名称)
其中CAST(Attribute.Name.query(``local-name(.)'')AS VARCHAR(100))如``％displayName％''
和(
Cast(Attribute.Name.value(''.''，``VARCHAR(100)'')AS VARCHAR(100))如''％Power％''
或
Cast(Attribute.Name.value(''.''，``VARCHAR(100)'')AS VARCHAR(100))如''％Pwr％''
)
按2排序
结束

从cur INTO @xmlCar
获取下一个
END

CLOSE cur
取消授权cur

I have a SQL table in DB that has a column which has data in XML. I need to parse the XML data column for each Row of the table and do a bulk insert into another table.

I tried using SQL and wrote a cursor which goes through each row of the table; looks for a string pattern in the attribute value of the XML and returns the result set. It does the job for me but it is slow and luckily there are few rows to process so it works but had it been more rows than it could have been a problem. I was wondering if there is any way SSIS can do the same job

Let''s say table name is EquipmentChartGroupLayout and the column which has XML data is equipmentchartlayout

declare cur cursor for select equipmentchartlayout.query(''(/carChart/item)'') FROM VIMSVehicle_Func_Sprint.Admin.EquipmentChartGroupLayout
declare @xmlCar xml

open cur

Fetch NEXT FROM cur INTO @xmlCar
While (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
begin
SELECT DISTINCT
CAST(Attribute.Name.query(''local-name(.)'') AS VARCHAR(100)) Attribute,
Cast(Attribute.Name.value(''.'',''VARCHAR(100)'') AS VARCHAR(100)) Value
FROM @xmlCar.nodes(''/item/@*'') Attribute(Name)
Where CAST(Attribute.Name.query(''local-name(.)'') AS VARCHAR(100)) like ''%displayName%''
and (
Cast(Attribute.Name.value(''.'',''VARCHAR(100)'') AS VARCHAR(100)) like ''%Power%''
Or
Cast(Attribute.Name.value(''.'',''VARCHAR(100)'') AS VARCHAR(100)) like ''%Pwr%''
)
order by 2
end

FETCH NEXT FROM cur INTO @xmlCar

END

CLOSE cur
DEALLOCATE cur

推荐答案

http://msdn.microsoft.com/en-US/library/ms189887(v=sql.90).aspx[^]

if you''re storing it as XML, you can search inside it.

这篇关于从具有XML列的SQL表中检索属性和值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！