XML as a Input Parameter in Stored Procedure.

IF EXISTS (SELECT 1 FROM sys.procedures WHERE object_id = object_id(N’XMLDOC’))
DROP PROCEDURE XMLDOC
GO
Create Procedure XMLDOC
@XMLDoc xml
as
begin

DECLARE @XmlDocumentHandle INT
EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @XMLDoc,'<ROOT xmlns:xyz=”urn:MyNamespace”/>’

select * FROM OPENXML (@XmlDocumentHandle,’/root/Resurce’,1)–with student
WITH (
ID int,
Name varchar(30),
Technology varchar(30),
EXP numeric(3,1)
)
EXEC sp_xml_removedocument @XmlDocumentHandle
end

exec XMLDOC @XMLDoc =
‘<root>
<Resurce
Name=”Mahesh”
ID=”1″
Technology=”DotNet”
EXP=”4.1″>
</Resurce>
<Resurce
Name=”Rajesh”
ID=”2″
Technology=”SQL, SSIS”
EXP=”3.4″>
</Resurce>
<Resurce
Name=”Praveen”
ID=”3″
Technology=”SSAS”
EXP=”4″>
</Resurce>
<Resurce
Name=”Sourabh Jain”
ID=”4″
Technology=”DWH, BO”
EXP=”10″>
</Resurce>
</root>’

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s