April 24, 2012 at 9:53 am
Hi,
I have XML data stored as an nvarchar(max) in a SQL Server 2005 table which i would like to process via SSIS.
The data stored in the table replaces data previously supplied as an XML file.
Therefore, I'd like to attempt to do the following in SSIS.
1 – Extract the data from the table and convert to XML (Use FOR XMl PATH syntax?)
2 – Generate an XSD schema for the XML data (how, is this possible?)
3 – Read the XML data into an SSIS variable
4 – Use the SSIS XML Source component to import the XML data (referencing the previously created XSD file- inline schema?)
Is this possible, and if so how would you recommend that it is achieved?
Thanks in advance
April 24, 2012 at 10:27 am
All of the steps you are identifying are definitely possible:
1 - No sense in using FOR XML, since you already have XML. Simply output that column.
2 - easiest way to do that is to save on of the XML items from step 1 to a file. You can then use either some XML tools (such as XMLSPY) to create a rough schema from the XML file, OR - when you use the XML Source component, it will prompt you for a schema andXML. If you supply the XML you can then click the "generate schema" option to generate the XSD.
3. In SSIS, this is done using a "FOREACH loop container" which is fed from a SQL task (This sets up the mechanism to loop through the records). I'd start by googling the Foreach loop container to see how it's set up. Once you have the container, the "inside" of the container is the set of steps you perform on each XML file. (Note: it will likely be cleaner to loop on the primary keys of the table, and retrive the XML within your loop).
4. once you start using the XML source component, and drag the green arrow to a given destination, it will prompt you which part of the XML is to be imported. Just match up however many of the elements to data source destinations.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 26, 2012 at 3:50 am
Hi,
Thanks for your prompt response.
We'll be trying to implement this over the next few days. Thanks for confirming it was achievable.
Regards,
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply