|
Home > Archive > BizTalk Server General > May 2004 > Using OpenXML statement in SQL Adapter
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Using OpenXML statement in SQL Adapter
|
|
| Kristine 2004-05-25, 4:37 pm |
| Is is possible to use the SQL Adapter to call a stored procedure that uses an OpenXML statement to insert the XML into SQL Server?
I am trying to find the best way to load a flat file with multiple records into a SQL Server database and would rather not use Update Grams.
| |
| Todd Sussman 2004-05-30, 10:46 am |
| If you want to use OpenXML in the Stored Procedure, you need to convert the
XML Document into a string. The string is then put inside the XML tag in
the SQL Adapter schema. It is a bit of a pain. What we did, was to create
a custom SQL adapter, that accepts XmlDocument as a parameter. On
www.gotdotnet.com they have an example of building custom adapters.
Good Luck
--
Todd Sussman
My opinion is neither copyrighted nor trademarked, and it's price
competitive. If you like, I'll trade for one of yours.
Remove The REMOVE! to reply.
"Kristine" <anonymous@discussions.microsoft.com> wrote in message
news:56CF574A-5C1B-453F-9AF3-A75B528C6D43@microsoft.com...
> Is is possible to use the SQL Adapter to call a stored procedure that uses
an OpenXML statement to insert the XML into SQL Server?
>
> I am trying to find the best way to load a flat file with multiple records
into a SQL Server database and would rather not use Update Grams.
>
| |
| Kristine 2004-05-30, 10:46 am |
| Thanks for the reply.
I have already converted the XML Document into a string, but I am having problems getting the string assigned to the XML tag. I distinquished the attribute for the XML string in the SQL Adapter schema and I used the message assignement shape in the orche
stration to create the message to send to the SQL Adapter, but when I compile I am getting an error stating that I am am using an unintialised message. The message is a multipart message for the receive root.
Here is the assignment:
XMLDoc = OrderMsg;
SPROC_input.parameters.InsertOrder.xmlOrder = XMLDoc.OuterXml;
Here is the error:
-'SPROC_input.parameters': message part has not been initialized in construct statement
-use of unassigned local variable 'SPROC_input.parameters'
-use of unconstructed message 'SPROC_input'
I think I need to initialise the root element and the stored procedure element of the Sql Adapter message as well, but I am not sure how to do that. I can not get the message that I send to the SQL Adapter to format correctly for the stored procedure.
Can you tell me how you are creating the message in the orchestration that calls the SQL Adapter?
----- Todd Sussman wrote: -----
If you want to use OpenXML in the Stored Procedure, you need to convert the
XML Document into a string. The string is then put inside the XML tag in
the SQL Adapter schema. It is a bit of a pain. What we did, was to create
a custom SQL adapter, that accepts XmlDocument as a parameter. On
www.gotdotnet.com they have an example of building custom adapters.
Good Luck
--
Todd Sussman
My opinion is neither copyrighted nor trademarked, and it's price
competitive. If you like, I'll trade for one of yours.
Remove The REMOVE! to reply.
"Kristine" <anonymous@discussions.microsoft.com> wrote in message
news:56CF574A-5C1B-453F-9AF3-A75B528C6D43@microsoft.com...
> Is is possible to use the SQL Adapter to call a stored procedure that uses
an OpenXML statement to insert the XML into SQL Server?
into a SQL Server database and would rather not use Update Grams.[vbcol=seagreen]
>
| |
| Todd Sussman 2004-05-30, 10:46 am |
| Add the line;
SPROC_input = new System.Xml.XmlDocument();
In addition, watch the string value in The SQL SP. When you use
XmlDocument.OuterXml you can have a problem with the "<" and ">". This is
why we decided to work with XmlDocuments in a custom adapter.
--
Todd Sussman
There are two major products that come out of Berkeley: LSD and UNIX. I
don't believe this to be a coincidence.
Remove the REMOVE! to reply.
"Kristine" <anonymous@discussions.microsoft.com> wrote in message
news:072E9B49-5D6A-4EDF-9ADF-C9A2075BFBFD@microsoft.com...
> Thanks for the reply.
>
> I have already converted the XML Document into a string, but I am having
problems getting the string assigned to the XML tag. I distinquished the
attribute for the XML string in the SQL Adapter schema and I used the
message assignement shape in the orchestration to create the message to send
to the SQL Adapter, but when I compile I am getting an error stating that I
am am using an unintialised message. The message is a multipart message for
the receive root.
>
> Here is the assignment:
> XMLDoc = OrderMsg;
> SPROC_input.parameters.InsertOrder.xmlOrder = XMLDoc.OuterXml;
>
> Here is the error:
> -'SPROC_input.parameters': message part has not been initialized in
construct statement
> -use of unassigned local variable 'SPROC_input.parameters'
> -use of unconstructed message 'SPROC_input'
>
> I think I need to initialise the root element and the stored procedure
element of the Sql Adapter message as well, but I am not sure how to do
that. I can not get the message that I send to the SQL Adapter to format
correctly for the stored procedure. Can you tell me how you are creating
the message in the orchestration that calls the SQL Adapter?
>
>
> ----- Todd Sussman wrote: -----
>
> If you want to use OpenXML in the Stored Procedure, you need to
convert the
> XML Document into a string. The string is then put inside the XML
tag in
> the SQL Adapter schema. It is a bit of a pain. What we did, was to
create[vbcol=seagreen]
> a custom SQL adapter, that accepts XmlDocument as a parameter. On
> www.gotdotnet.com they have an example of building custom adapters.
>
> Good Luck
>
> --
> Todd Sussman
> My opinion is neither copyrighted nor trademarked, and it's price
> competitive. If you like, I'll trade for one of yours.
>
> Remove The REMOVE! to reply.
>
> "Kristine" <anonymous@discussions.microsoft.com> wrote in message
> news:56CF574A-5C1B-453F-9AF3-A75B528C6D43@microsoft.com...
that uses[vbcol=seagreen]
> an OpenXML statement to insert the XML into SQL Server?
records[vbcol=seagreen]
> into a SQL Server database and would rather not use Update Grams.
|
|
|
|
|