2

I'm trying to Insert two columns from one table onto another. The first column is an int but the second column is an XML. I'm having issues inserting the xml column

enter image description here

In first SQL Task I'm doing a select of the the two columns and storing this information into an object.

enter image description here

In the for loop I'm passing on the information :

enter image description here

And in the Result set I'm declaring the variables : enter image description here

I know that the Form_XML_Text is set to Varchar here but there is not XML option.

And running this insert statement: INSERT INTO TEST VALUES (?,?)

This is the error message I'm getting back:

enter image description here

  • Can you replace your images with text or even better a reproduction on dbfiddle.uk please? – George.Palacios Mar 27 at 12:05
  • Also can you please add your version of SQL Server as a tag? – George.Palacios Mar 27 at 12:05
  • @George.Palacios why replace with text, it's clearer with images what I'm trying explain? – Rachel Mar 27 at 12:07
  • So it's part of the site's general rules to make it as easy as possible for people to Answer questions - using images makes it harder to copy code out to test it etc. Secondly, Imgur is blocked for me at work so I physically can't see them! Basically it might be easier for YOU to post an image, but its significantly easier for someone to answer your question if you post all relevant code and expected output as text – George.Palacios Mar 27 at 12:27
  • @George.Palacios it isn't SQL code it's images of SSIS packages so pasting to dbfiddle is unlikely to help. – Zane Mar 27 at 14:53
0

This can be done by converting the XML column when initially selecting it then casting it back to an XML data type for the insert. This is outlined in further detail below.

  • In the first Execute SQL Task, select the XML column by casting it to an NVARCHAR data type. Keep the Form_XML_Text variable as a string data type.
  • In the Execute SQL Task within the Foreach Loop, set this variable to use the NVARCHAR data type on the Parameter Mapping pane.
  • Then in the SQL statement on the Execute SQL Task inside the loop cast it back to XML as done below. This example assumes that the XML column is the second one in the destination table.
  • I would also recommend always explicitly listing the columns when inserting into a table as follows. This will make debugging easier and prevent errors that can occur if additional columns are added to the table in future.

Insert Example:

INSERT INTO TEST (Column1, XMLColumn)
VALUES (?, CAST(? AS XML))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.