How to extract a column with xml data and load it into another table

Question

Hi All,

I have a table with couple of columns, that have xml data, how can I extract the data into another table as columns using SSIS. Please any help is appreciated.

Thanks

Answer 1

Hi @Bigiron,

Please try the following solution.

-- DDL and sample data population, start
DECLARE @tbl TABLE (id uniqueidentifier, category nvarchar(max), fees nvarchar(max));
INSERT INTO @tbl (id, category, fees) VALUES
('5ED9CBEA-4C0F-45AB-A327-B8DD711950C6',
N'<Category>
	<Attributes>
		<Attribute>
			<Name>Make</Name>
			<Value>Hoelscher</Value>
		</Attribute>
		<Attribute>
			<Name>Model</Name>
			<Value>50</Value>
		</Attribute>
		<Attribute>
			<Name>ItemType</Name>
			<Value>Skid Steer Bale Accumulator</Value>
		</Attribute>
		<Attribute>
			<Name>SerialNumber</Name>
			<Value>01258</Value>
		</Attribute>
		<Attribute>
			<Name>IntendedforHighwayUse</Name>
			<Value>No</Value>
		</Attribute>
		<Attribute>
			<Name>MilesNotActual</Name>
			<Value>No</Value>
		</Attribute>
	</Attributes>
	<ExternalId>0</ExternalId>
	<Name>SkidSteers-Attachments</Name>
	<Ordinal>0</Ordinal>
</Category>',
N'<Fees><Items /></Fees>');
-- DDL and sample data population, end

SELECT t.id
	, c.value('(Name/text())[1]', 'VARCHAR(30)') AS [Name]
	, c.value('(Value/text())[1]', 'VARCHAR(30)') AS [Value]
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST(category AS XML)) AS t1(x)
CROSS APPLY x.nodes('/Category/Attributes/Attribute') AS t2(c);

Output

id	Name	Value
5ED9CBEA-4C0F-45AB-A327-B8DD711950C6	Make	Hoelscher
5ED9CBEA-4C0F-45AB-A327-B8DD711950C6	Model	50
5ED9CBEA-4C0F-45AB-A327-B8DD711950C6	ItemType	Skid Steer Bale Accumulator
5ED9CBEA-4C0F-45AB-A327-B8DD711950C6	SerialNumber	01258
5ED9CBEA-4C0F-45AB-A327-B8DD711950C6	IntendedforHighwayUse	No
5ED9CBEA-4C0F-45AB-A327-B8DD711950C6	MilesNotActual	No

Answer 2

create table [dbo].[Items1] (id uniqueidentifier,category nvarchar(max),fees nvarchar(max))
  insert into [dbo].[Items1]
  VALUES ('B1BEA949-CA34-498-9C7A-00007C27BEB6','<Category><Attributes><Attribute><Name>Make</Name><Value>Hoelscher</Value></Attribute><Attribute><Name>Model</Name><Value>50</Value></Attribute><Attribute><Name>ItemType</Name><Value>Skid Steer Bale Accumulator</Value></Attribute><Attribute><Name>SerialNumber</Name><Value>01258</Value></Attribute><Attribute><Name>IntendedforHighwayUse</Name><Value>No</Value></Attribute><Attribute><Name>MilesNotActual</Name><Value>No</Value></Attribute></Attributes><ExternalId>0</ExternalId><Name>SkidSteers-Attachments</Name><Ordinal>0</Ordinal></Category>',
  '<Fees><Items /></Fees>)

I want to extract the data from the 2 columns 'category' and 'fees' either using SSIS or TSQL

It should look like the image below for category

Thanks

Answer 3

Hi @Bigiron,

You may also check this article about using SSIS to load xml file to sql server table.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.