Create delimited file from SQL XML column type

Question

Create delimited file from SQL XML column type

jimsefton

SSC Rookie

Points: 31
More actions
January 4, 2017 at 6:55 am

#339178

I need to create a delimited file from an XML column in SQL. i had initially tried doing this by creating a view in MSSQL, but quickly found out there are over 5000 XML tags in the field so hit the limits of max number of columns.
My plan now it to use SSIS in this way:
OLE DB source to the SQL DB
Use an XSLT to transform the XML into basic XML (I have tested this using input and output files)
Dump a delimited file of the basic XML
Does this sound correct? If so can someone give be an idea of the best way to do it. I am struggling with whether to use an Execute SQL Task or a data flow for starters.

Viewing 4 posts - 1 through 3 (of 3 total)

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

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

I think what you're wanting to do is shred XML (which has been done before, see here [/url]plus a google search will return a lot of results). My concern would be the XML itself. THe tag count isn't so much an issue if the tag represents a single row. The potential for (effectively) embedded tables within the XML structure would worry me more because a flat file/csv is effectively a representation of a single entity/table.

Steve.

jimsefton SSC Rookie Points: 31 More actions · Answer 2

Thanks Steve, I had a look on google but must have missed the example you posted. That does look like it might work so long as I don't do the insertion into the SQL table, as that hits the max SQL limit (if I understand correctly).

I am not worried about the potential for nesting, this is strictly XML related to a single entity and is just attribute/value pairs. From what I read that might be a problem in itself, as SSIS expects basic XML, so I may need to apply an XSLT to transform it as part of the process in the tutorial.

I'll give that a go now and let you know how I get on.

jimsefton SSC Rookie Points: 31 More actions · Answer 3

Just had another thought. The XML doesn't always have the same fields. I guess this works if I am using SQL as it maps to column names, but for CSV I would need to apply an XSD. Going via SQL makes more sense, but I guess I would need to use a wide table.... but then there could be issues with row size limits?