|
Home > Archive > BizTalk Server > October 2005 > Problem in passing parameter to Stored Procedure using 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 |
Problem in passing parameter to Stored Procedure using SQL adapter
|
|
| Shipra Biswas 2005-10-24, 10:32 am |
| Hi,
I have written a Stored Procedure which takes in just 1 paramater, which is
XML Data.
The Stored Procedure is as follows:
CREATE PROCEDURE UpdateVendorProducts
@xmlDocument ntext
AS
BEGIN
DECLARE @docHandle int
DECLARE @cmd sysname
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
Declare @vendorID int
Select *
From Vendors as V,
OPENXML (@docHandle, '/Vendor', 1)
With ( UserName nvarchar(50) './@UserName',
[PassWord] nvarchar(50) './@Password'
) as XMLDoc
Where
V.UserName = XMLDoc.UserName
And V.[Password] = XMLDoc.[Password]
Select @vendorID = VendorID
From Vendors as V,
OPENXML (@docHandle, '/Vendor', 1)
With ( UserName nvarchar(50) './@UserName',
[PassWord] nvarchar(50) './@Password'
) as XMLDoc
Where
V.UserName = XMLDoc.UserName
And V.[Password] = XMLDoc.[Password]
if(@@RowCount = 0)
BEGIN
Raiserror('Invalid Vendor UserName/Password', 16, 1)
Return
END
INSERT INTO ProductAvailability
SELECT P.ProductID, @VendorID as VendorID, GETDATE(), UnitPrice, Quantity
FROM Products as P,
OPENXML (@docHandle, '/Vendor/Products/Product', 1)
WITH ( ProductKey varchar(50) './@ProductKey',
UnitPrice decimal './@Price',
Quantity int './@Quantity'
) as XMLDoc
Where
P.ProductKey = XMLDoc.ProductKey
EXEC sp_xml_removedocument @docHandle
END
GO
This is the way i execute the stored procedure:
exec updatevendorproducts '<Vendor UserName="sumitp" Password="sumitp">
<Products>
<Product ProductKey="Logitech KeyBoard" Price="785" Quantity="17"/>
<Product ProductKey="Samsung Monitor" Price="35" Quantity="2"/>
<Product ProductKey="i-Ball KeyBoard" Price="544" Quantity="2"/>
</Products>
</Vendor>'
I want to use this stored procedure from a BizTalk Orchestration using SQL
Adapter. When i created the Adapter, i got the SQLService Schema with
Request Schema and Response Schema as follows:
<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified"
targetNamespace="http://DemoFlatFileToXML" version="1.0"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Request">
<xs:complexType>
<xs:sequence>
<xs:element name="UpdateVendorProducts">
<xs:complexType>
<xs:attribute name="xmlDocument" type="xs:string" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Response">
<xs:complexType>
<xs:sequence>
<xs:element name="Success" type="xs:anyType" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
The problem i am facing here is, I have to send the request as:
<?xml version="1.0" encoding="utf-8" ?>
<ns0:Request xmlns:ns0="http://DemoFlatFileToXML">
<ns0:UpdateVendorProducts xmlDocument="<Vendor UserName="sumitp"
password="sumitp"><Products><Product ProductKey="i-Ball KeyBoard"
Price="777" Quantity="2"/></Products></Vendor>" />
</ns0:Request>
For this, I have created a BizTalk Message which contains:
<Vendor UserName="sumitp" password="sumitp"><Products><Product
ProductKey="i-Ball KeyBoard" Price="777" Quantity="2"/></Products></Vendor>
But I dont know how to assign the value to the above message to the
SQLRequest Message which I have to create. Any help in this respect is
appreciated.
Thanks in advance
Shipra Biswas
| |
| Matt Milner 2005-10-24, 10:32 am |
| you can check out my blog posting [1] where I provide a sample of how to do
this. You're most of the way there, you just have to connect a couple of
dots.
Matt
[1]
http://www.m3technologypartners.com...72-d79cba73dcc2
"Shipra Biswas" <shiprab@MAQsoftware.com> wrote in message
news:%23BxC5%23t0FHA.1032@TK2MSFTNGP12.phx.gbl...
> Hi,
> I have written a Stored Procedure which takes in just 1 paramater, which
> is XML Data.
>
> The Stored Procedure is as follows:
>
> CREATE PROCEDURE UpdateVendorProducts
> @xmlDocument ntext
> AS
>
> BEGIN
> DECLARE @docHandle int
> DECLARE @cmd sysname
>
> EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
>
> Declare @vendorID int
>
> Select *
> From Vendors as V,
> OPENXML (@docHandle, '/Vendor', 1)
> With ( UserName nvarchar(50) './@UserName',
> [PassWord] nvarchar(50) './@Password'
> ) as XMLDoc
> Where
> V.UserName = XMLDoc.UserName
> And V.[Password] = XMLDoc.[Password]
>
> Select @vendorID = VendorID
> From Vendors as V,
> OPENXML (@docHandle, '/Vendor', 1)
> With ( UserName nvarchar(50) './@UserName',
> [PassWord] nvarchar(50) './@Password'
> ) as XMLDoc
> Where
> V.UserName = XMLDoc.UserName
> And V.[Password] = XMLDoc.[Password]
>
> if(@@RowCount = 0)
> BEGIN
> Raiserror('Invalid Vendor UserName/Password', 16, 1)
> Return
> END
>
> INSERT INTO ProductAvailability
> SELECT P.ProductID, @VendorID as VendorID, GETDATE(), UnitPrice, Quantity
> FROM Products as P,
> OPENXML (@docHandle, '/Vendor/Products/Product', 1)
> WITH ( ProductKey varchar(50) './@ProductKey',
> UnitPrice decimal './@Price',
> Quantity int './@Quantity'
> ) as XMLDoc
> Where
> P.ProductKey = XMLDoc.ProductKey
>
> EXEC sp_xml_removedocument @docHandle
> END
> GO
>
> This is the way i execute the stored procedure:
> exec updatevendorproducts '<Vendor UserName="sumitp" Password="sumitp">
> <Products>
> <Product ProductKey="Logitech KeyBoard" Price="785" Quantity="17"/>
> <Product ProductKey="Samsung Monitor" Price="35" Quantity="2"/>
> <Product ProductKey="i-Ball KeyBoard" Price="544" Quantity="2"/>
> </Products>
> </Vendor>'
>
> I want to use this stored procedure from a BizTalk Orchestration using SQL
> Adapter. When i created the Adapter, i got the SQLService Schema with
> Request Schema and Response Schema as follows:
>
> <?xml version="1.0"?>
> <xs:schema attributeFormDefault="unqualified"
> elementFormDefault="qualified" targetNamespace="http://DemoFlatFileToXML"
> version="1.0" xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:element name="Request">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="UpdateVendorProducts">
> <xs:complexType>
> <xs:attribute name="xmlDocument" type="xs:string" />
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:element name="Response">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="Success" type="xs:anyType" />
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:schema>
>
> The problem i am facing here is, I have to send the request as:
>
> <?xml version="1.0" encoding="utf-8" ?>
> <ns0:Request xmlns:ns0="http://DemoFlatFileToXML">
> <ns0:UpdateVendorProducts xmlDocument="<Vendor UserName="sumitp"
> password="sumitp"><Products><Product ProductKey="i-Ball KeyBoard"
> Price="777" Quantity="2"/></Products></Vendor>" />
> </ns0:Request>
>
> For this, I have created a BizTalk Message which contains:
> <Vendor UserName="sumitp" password="sumitp"><Products><Product
> ProductKey="i-Ball KeyBoard" Price="777"
> Quantity="2"/></Products></Vendor>
>
> But I dont know how to assign the value to the above message to the
> SQLRequest Message which I have to create. Any help in this respect is
> appreciated.
>
> Thanks in advance
> Shipra Biswas
>
|
|
|
|
|