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