Load a XML file to a variable

When I look into the XML Source component, there is an option of XMldata from varialbe, can anybody help me in knowing how exactly do I go about in loading an XML file onto a variable ?

Variety of ways to load xml data from a file into a variable. Here's one:

One is to use the XML task with a no-op xslt. The xsl transform doesn't do anything, so the unmodified file data ends up the variable.

Operation Type: XSLT

SourceType: File connection

SaveOperationResult: True

Destination: SomeStringVariable

DestinationType: Variable

OverwriteDestination: True

SecondOperandType: Direct input

Second Operand:

Code Snippet

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output encoding="utf-16" />
<xsl:template match="*">
<xsl:copy-of select = "." />
</xsl:template>

</xsl:stylesheet>

|||

Thanks a lot for the reply!!!

But this is what am trying to do.. I have a variable onto which am storing the complete path location where my XML file is stored. Say "C:\\Temp\\A.xml" I also have a Stored Proc in my Database that does accept an XML doc as an input parameter. Now is there a way I can pick the XML file load it onto a variable and pass it onto the Stored Proc

|||Starting from the beginning, and attempting to get the contents of a xml file into a SQL 2k5 stored procedure's input xml parameter...

So, supposing you had a source xml like the following

<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<book>
<chapter name="all play and no work"></chapter>
<chapter name="all work and no play"></chapter>
</book>

and a stored procedure which counted up the number of chapters

CREATE PROCEDURE dbo.CountOfChapters
(
@.doc xml
)
AS
SET NOCOUNT ON
SELECT @.doc.value('count(//chapter)','int') as CountOfChapters
RETURN

First, create an XML task, using the no-op xslt set to the following xsl transform to get the data into an IS string variable

Code Snippet

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output encoding="utf-16" />
<xsl:template match="*">
<xsl:copy-of select = "." />
</xsl:template>

</xsl:stylesheet>

Follow that XML task with an Execute SQL task. Use an ADO.NET SqlClient connection manager, and in the Parameter Mapings table, map the sproc's @.doc input parameter to a DataType of Xml and to the String variable loaded by the XSLT task. Set the ResultProperty of the SQL task to "Single Row", mapping into an IS variable of type In32. A count of 2 is loaded into the IS variable.
|||

jaegd wrote:

First, create an XML task, using the no-op xslt set to the following xsl transform to get the data into an IS string variable

<?xml version="1.0"?>
<xsltylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xslutput encoding="utf-16" />
<xsl:template match="*">
<xsl:copy-of select = "." />
</xsl:template>

</xsltylesheet>

Follow that XML task with an Execute SQL task. Use an ADO.NET SqlClient connection manager, and in the Parameter Mapings table, map the sproc's @.doc input parameter to a DataType of Xml and to the String variable loaded by the XSLT task. Set the ResultProperty of the SQL task to "Single Row", mapping into an IS variable of type In32. A count of 2 is loaded into the IS variable.

Sorry to ask u again, but can you please explain a bit here ? I have a XML task, operation type i have given it as XSLT, Source type I have selected as File Connection, given a value for the source field, Selected Destination as the string variable to be passed into the SP. What is this Second operand stuff ?

This is slightly confusing. Sorry if am troubling...

|||The second operand for the XML task, when then operation is XSLT, is the Xml tranform text itself. SecondOperand is an "accurate" name, but it not a helpful one.

Paste the text of the xml transform into the SecondOperand property. The problem might be that the forum's "smiley" converter trashes (by default) cut and paste of xml, xml schema, xml transforms.

The xsltylesheet really should read "xsl" colon "stylesheet"
The xslutput, which is an interesting conversion of what was originally pasted, should read "xsl" colon "output".

|||

Try enclosing the code in a code snippet.

Code Snippet

xsl:stylesheet

xsl:output

|||Thanks a lot. I'll use that from now on.
|||

jaegd wrote:

The second operand for the XML task, when then operation is XSLT, is the Xml tranform text itself. SecondOperand is an "accurate" name, but it not a helpful one.

Paste the text of the xml transform into the SecondOperand property. The problem might be that the forum's "smiley" converter trashes (by default) cut and paste of xml, xml schema, xml transforms.

The xsltylesheet really should read "xsl" colon "stylesheet"
The xslutput, which is an interesting conversion of what was originally pasted, should read "xsl" colon "output".

Thanks a Million it is working now.. But have a slight query over here. you had said about having the Coonection in Souce type in XML Task as File Connection right ? Am afraid it is loading only that particular XML File. How do I make it pick all the XML files in the particaular path ? Is this possible ? So close yet so far ....

|||

I did try out this..

I got a varialbe totFileName that has the name of the XMl file and the complete path where it si stored, am getign this variable populated with all the xml file names, thanks to the script/for each component. I modified the source type in the XMl Task to Variable instead of File Connection, clicked on the Expressions builder and for the property souce I came up with the expression "@.[User::TotFileName]" and when I clicked on the evaluate expression, I get the complete path name, "C:\\Documents and Settings\\temp\\S_ 22328_0003.xml".

But when I run the package this is the error I get. "Error: Failed to lock variable "S_17470_0003b652.xml" for read/write access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Something am missing here ?

|||

When processsing multiple files, use a ForEach loop container (with a File Enumerator ) with appropriate file name to variable mappings.

The set of contained tasks is the same (XML Task + Execute SQL task) for this problem space. Again, there's almost always more than one way to do it; this will work.

|||

jaegd wrote:

When processsing multiple files, use a ForEach loop container (with a File Enumerator ) with appropriate file name to variable mappings.

The set of contained tasks is the same (XML Task + Execute SQL task) for this problem space. Again, there's almost always more than one way to do it; this will work.

This is eating me head... Ok I am doing just what you have specified, I have a ForEachLoop task (node list), Inside which I wrote a Script to get the name of the XML files, and then I have one more ForEach Task with file enumerator. Set of contained task is XML+ExecuteSQl. Now my idea is try to create the directory in which my XML's are loaded dynamically using a variable, then pass the name of the XML to the XML task and load the XML into a varialbe and pass it onto the SP. Looks so simple but no luck its still giving the same error, I had posted earlier. Anyhting I am missing here. Thanks for all the help so far...

|||

The place to put the dynamic filename expression is on the file connection manager's connectionString property.

Change the source type of the connection back from Variable to File Connection on XML Task. Put the expression @.[User::TotFileName] on the File Connection manager's ConnectionString property.

The reason for that "variable cannot be found" error is quite simple. You have changed the SourceType on the XML Task from File Connection to Variable, and then put an expression on the XML Task's Source property. Translated, that means the task is going to look for a variable named "S_17470_0003b652.xml", which doesn't and shouldn't exist.

The interpretation of the Source property for the XML Task is based on the Source Type (Direct Input, File connection, or Variable) When the SourceType is "Variable", the task's Source property must be the NAME of a variable, which contains xml. Naturally there is no variable with the name "S_17470_0003b652.xml", that's the name of a file. Now, when the SourceType is "File Connection", the Source task property must be the NAME of a file connection manager, which points to a file containing xml.