Process Records in XML Column

Question

Process Records in XML Column

Gerbera

SSCommitted

Points: 1715
More actions
April 2, 2012 at 7:21 am

#271268

I have each record stored in one Single XML Column in SQL server.
Can anyone suggest how to process all records in SSIS
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

Process all records how? Out to a flat file? Into another database? Please provide more detail about what you're trying to do. Also, posting your table definitions may help us determine a path forward as well.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Gerbera SSCommitted Points: 1715 More actions · Answer 2

Source : I have one XML column which has data(eg:<BI><Detail><Name /> <DOB /> <Detail/><Control> <Date/></Control></BI>) and 7 other columns holding information like ID, Date, Sytem etc..

Target : Flat File (Need to load target with the fields present in XML Column)

Flat file fields : Name DOB Date

Also need to update the Source table with Processed date for each record after fetching XMl column data

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

In your SSIS Data Flow, you can issue SQL that looks like this in your Data Source Component (e.g. OLE DB Source) to get the data out of the database in tabular format:

-- syntax is SQL 2008 or above

DECLARE @target_data XML = N'

<BI>

<Detail>

<Name>John</Name>

<DOB>1978-01-01</DOB>

</Detail>

<Control>

<Date>2012-04-03</Date>

</Control>

</BI>';

SELECT @target_data.value('(BI/Detail/Name)[1]', 'varchar(100)') AS name,

@target_data.value('(BI/Detail/DOB)[1]', 'varchar(100)') AS dob,

@target_data.value('(BI/Control/Date)[1]', 'varchar(100)') AS control_date;

As for updating the processed date, you may need to do things inside a transaction where you update the processed date first, then select out the rows with the new processed date, then commit the transaction. You can do all of this inside a stored proc to guarantee you only take the rows for the current run.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Gerbera SSCommitted Points: 1715 More actions · Answer 4

I have a huge nodes in XML. So my SELECT statement goes little long..

My approach : -> XML column to recordset, For Each Recordset-->Dataflow(XML Source from Variable to Flat file destination)

Is this okay?

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

Gerbera (4/4/2012)
I have a huge nodes in XML. So my SELECT statement goes little long..
My approach : -> XML column to recordset, For Each Recordset-->Dataflow(XML Source from Variable to Flat file destination)
Is this okay?

In terms of it being a technically viable way to go, sure, it is okay. It's up to you where you want to place the processing and programming burden. You can do the XML shredding using SSIS, or using T-SQL. Both can be coded to yield the exact same result. You say your SQL statement will be a little long...what does that mean?

<my_personal_preference>After having worked with XML in both areas, when processing XML file into SQL Server I would use SSIS to shred the data before bulk loading, but for taking XML out of SQL Server I would use T-SQL, even if it means writing a long and complex SELECT statement, to get a tabular resultset into SSIS.</my_personal_preference>

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato