SSIS Import Column Set XML
-
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
-
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
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
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 -
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
-
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
-
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 -
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.
-
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
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