none
Extracting XML data stored in a SQL Server table column as nvarchar(max)

    Question

  • I have XML data stored in a SQL Server table column as nvarchar(max). Here is a sample of the data in this column:

    <?xml version="1.0" encoding="utf-16"?> <ValueWorkSheet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <Element Name="Services"> <Element Name="Service"> <Field Name="Service" Value="Travel Assistance" /> <Field Name="ServicePremium" Value="0" /> </Element> <Field Name="TotalServicesPremium" Value="0" /> </Element> <Element Name="Classes"> <Element Name="Class"> <Element Name="ConditionsOfCoverages"> <Element Name="ConditionsOfCoverage"> <Field Name="ConditionOfCoverage" Value="Transportation - Common Carrier Business Only" /> <Field Name="ConditionOfCoverageValue" Value="0.0178" /> </ValueWorkSheet>

     

    I would like to parse this data in SSIS and load it into SQL Server with separate columns for each field name and field name value. What would be the best way to accomplish this in SSIS?

     

    Thank you


    davidg12
    Friday, December 23, 2011 6:49 PM

Answers

  • There is no such a source component in SSIS that can process XML right out of the database field storage.

    Besides, you do not need SSIS for what you need to accomplish.

    I indeed suggest you make this field of XML Type ad then

    I would do that with a XPath query like:

    SELECT * INTO MyTable FROM Customers 
        WHERE Class.value('(/Class)[1]', 'nvarchar(1000)') = 'Economy'
    

    Hope you understood the concept.


    PS: Excuse me if the query does not compile, but it must be close to what you need.


    Arthur My Blog
    • Marked as answer by Eileen Zhao Friday, December 30, 2011 2:57 AM
    Friday, December 23, 2011 8:41 PM
  • you can fetch the data out from that column by execute sql task, with a simple select query

    then fill result into a string variable, then use XML task with XSLT on the string variable and then use result in xml source of data flow task.

    also you can loop through records if you have more than one record with xml content.

    another way is using XQUERY which you can read a lot about it here:

    http://msdn.microsoft.com/en-us/library/ms345122%28v=sql.90%29.aspx


    http://www.rad.pasfu.com
    • Marked as answer by Eileen Zhao Friday, December 30, 2011 2:57 AM
    Friday, December 23, 2011 8:46 PM

All replies

  • you can use XML Task with XSLT operation to flatten this xml, a sample can be found here:

    http://www.rad.pasfu.com/index.php?/archives/21-XML-Task-Changing-Style-of-Data-XSLT.html

    then you can use XML Source in the data flow task to read it and load it to any destination you like.


    http://www.rad.pasfu.com
    Friday, December 23, 2011 7:09 PM
  • My question is a bit more basic at this point. I want to extract xml data that's sitting in a sql server table column stored as a nvarchar(max). I can convert this to an xml datatype in SQL. However, the XML Source in SSIS  appears to require a file rather than pulling the xml data from a column in sql server. Is there any method to use the xml source against a column in sql server?
    davidg12
    Friday, December 23, 2011 8:29 PM
  • There is no such a source component in SSIS that can process XML right out of the database field storage.

    Besides, you do not need SSIS for what you need to accomplish.

    I indeed suggest you make this field of XML Type ad then

    I would do that with a XPath query like:

    SELECT * INTO MyTable FROM Customers 
        WHERE Class.value('(/Class)[1]', 'nvarchar(1000)') = 'Economy'
    

    Hope you understood the concept.


    PS: Excuse me if the query does not compile, but it must be close to what you need.


    Arthur My Blog
    • Marked as answer by Eileen Zhao Friday, December 30, 2011 2:57 AM
    Friday, December 23, 2011 8:41 PM
  • you can fetch the data out from that column by execute sql task, with a simple select query

    then fill result into a string variable, then use XML task with XSLT on the string variable and then use result in xml source of data flow task.

    also you can loop through records if you have more than one record with xml content.

    another way is using XQUERY which you can read a lot about it here:

    http://msdn.microsoft.com/en-us/library/ms345122%28v=sql.90%29.aspx


    http://www.rad.pasfu.com
    • Marked as answer by Eileen Zhao Friday, December 30, 2011 2:57 AM
    Friday, December 23, 2011 8:46 PM
  • My question is a bit more basic at this point. I want to extract xml data that's sitting in a sql server table column stored as a nvarchar(max). I can convert this to an xml datatype in SQL. However, the XML Source in SSIS  appears to require a file rather than pulling the xml data from a column in sql server. -- NOT exactly true check other options as well for data access modes in xml source editor 
    Is there any method to use the xml source against a column in sql server?

    davidg12

    What probably you can do is create a xmlData variable in ssis populate this variable in control flow via execute sql task and loop through it for all or whatever numbers of records . then in same loop use data flow where xml source option u can use as daat access mode as xml data from variable ... 


    Hope that helps ... Kunal
    Friday, December 23, 2011 8:54 PM
  • Another Solution could be

    Select Oledb Source : Select  xmlDataColumn FROM YOUR tableName

    Use Script component transformation add Output coulmns [your fields in xml data ] read your xmlData from source in script component parse it into xmldoc or so then read the xml nodes and write the values into your script component output columns ...


    Hope that helps ... Kunal
    Friday, December 23, 2011 9:55 PM