Retrieve attributes and values from a SQL table which has a XML column

See more:

XML

SSIS

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