Post reply

SSIS Import Column Set XML

  • January 26, 2016 at 10:40 pm

    #307925

    Hi All,

    I have a SQL table with a column data type 'Column Set,XML(.),null) called tp_columnset and not all the rows have data but some <XML> entries. I am trying to import these into another table, as I would like to convert the data from XML to Varchar or whatever is required for reporting.

    I have added a Execute SQL Task with a connection to the table & column and a select * from tablewhere tp_columnset is not null with connection as result set XML and result set is mapped to a variable columnset and result name as 0. I have tried changing the variable between object & string types.

    In the data flow task I have tried using XML source with XML from variable but it says the variable is empty. I added a break point 'break when variable values changes' on the 'execute sql task' but i am unable to enter the variable name the watch window is grayed out.

    I am doing something wrong not sure why the variable is empty and i can't add it to the watch window.(see attached screen shots)

    Thanks

  • January 27, 2016 at 6:18 am

    #1854658

    ringovski (1/26/2016)

    Hi All,

    I have a SQL table with a column data type 'Column Set,XML(.),null) called tp_columnset and not all the rows have data but some <XML> entries. I am trying to import these into another table, as I would like to convert the data from XML to Varchar or whatever is required for reporting.

    I have added a Execute SQL Task with a connection to the table & column and a select * from tablewhere tp_columnset is not null with connection as result set XML and result set is mapped to a variable columnset and result name as 0. I have tried changing the variable between object & string types.

    In the data flow task I have tried using XML source with XML from variable but it says the variable is empty. I added a break point 'break when variable values changes' on the 'execute sql task' but i am unable to enter the variable name the watch window is grayed out.

    I am doing something wrong not sure why the variable is empty and i can't add it to the watch window.(see attached screen shots)

    Thanks

    I can't see a need for ExecuteSQL in this case.

    Just a data flow task with a source query like this

    select Col1 = cast(XMLCol as varchar(max))

    where XMLCol is not null

    should be sufficient

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • January 27, 2016 at 7:02 am

    #1854674

    I think they want to shred the XML column contents in the context of the Package and load the resulting data into standard tables on the downstream.

    @ringovski can you clarify what you're trying to do?

    If you just want to mave the XML as a chunk into a table on the destination Phil has the solution for you, don't waste time with Execute SQL Task. If you had something else in mind post back more details.

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

  • January 27, 2016 at 7:16 am

    #1854683

    Orlando Colamatteo (1/27/2016)

    I think they want to shred the XML column contents in the context of the Package and load the resulting data into standard tables on the downstream.

    If that is indeed what the OP wants to do then they can use SQL Server's built-in XML methods or, IIRC, there is a custom component produced by somebody called XMLIFY that does the job nicely - I've used it many moons ago.

    A script task would also work....provided you can write the script correctly! 😉

    Regards

    Lempster

  • January 27, 2016 at 9:02 pm

    #1854862

    The end result i am trying to achieve is to export the XML into another table or tables depending on the data structure, then write some reports via SSRS.

    select Col1 = cast([tp_ColumnSet] as varchar(max))

    from [dbo].[Data]

    where [tp_ColumnSet] is not null

    Msg 6355, Level 16, State 1, Line 1

    Conversion of one or more characters from XML to target collation impossible

  • January 27, 2016 at 9:10 pm

    #1854863

    It is still not clear to me what you are trying to do.

    Are you trying to copy data from an XML column in one server to an XML column on another server? Or are you looking to copy data from an XML column in one server and transform it so it can be loaded into one or more tables into non-XML type columns on another server? Or something else?

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

  • January 27, 2016 at 9:13 pm

    #1854864

    Orlando Colamatteo (1/27/2016)

    It is still not clear to me what you are trying to do.

    Or are you looking to copy data from an XML column in one server and transform it so it can be loaded into one or more tables into non-XML type columns on another server?

    This, don't have much experience with XML data so not sure how to approach it.

  • January 28, 2016 at 6:18 am

    #1854922

    ringovski (1/27/2016)

    Orlando Colamatteo (1/27/2016)

    It is still not clear to me what you are trying to do.

    Or are you looking to copy data from an XML column in one server and transform it so it can be loaded into one or more tables into non-XML type columns on another server?

    This, don't have much experience with XML data so not sure how to approach it.

    Orlando's question is intended to find out whether you want to keep the entire XML document in a single column, or whether you want to break the document down and store its elements in separate columns.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • January 28, 2016 at 6:19 am

    #1854923

    ringovski (1/27/2016)

    The end result i am trying to achieve is to export the XML into another table or tables depending on the data structure, then write some reports via SSRS.

    select Col1 = cast([tp_ColumnSet] as varchar(max))

    from [dbo].[Data]

    where [tp_ColumnSet] is not null

    Msg 6355, Level 16, State 1, Line 1

    Conversion of one or more characters from XML to target collation impossible

    You could try nvarchar(max) to see whether that helps.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • January 28, 2016 at 8:56 am

    #1854992

    ringovski (1/27/2016)

    Orlando Colamatteo (1/27/2016)

    It is still not clear to me what you are trying to do.

    Or are you looking to copy data from an XML column in one server and transform it so it can be loaded into one or more tables into non-XML type columns on another server?

    This, don't have much experience with XML data so not sure how to approach it.

    I think I got your intent now. The XML ResultSet type of the Execute SQL Task is not meant to handle data from an XML column. It is meant to handle the results of queries that return an XML document as the entire resultset, e.g. queries that make use of FOR XML.

    To get the value from a specific row's XML column into a variable and use that as the XML in a Data Flow's XML Source component you can follow this pattern, in pictures. Post back if you have questions:

    EDIT: resize pics

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

Viewing 10 posts - 1 through 9 (of 9 total)

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