Monday, 24 November 2014

XML as input parameter to SP




<?xml version="1.0" encoding="UTF-8"?>
<tns:EReplyMsg>
<typ:EId>1</typ:EId>
<typ:Ts>
<typ:T>
<typ:AType>12345</typ:AType>
<typ:SLine>SUCCESS</typ:SLine>
<typ:Code>ABC</typ:Code>
<typ:Desc>Failed</typ:Desc>
<typ:SM>
<typ:From>ABC</typ:From>
<typ:To>DEF</typ:To>
<typ:Number>1.0</typ:Number>
</typ:SM>
</typ:T>
<typ:T>
<typ:AType>67890</typ:AType>
<typ:SLine>FAILED</typ:SLine>
<typ:Code>DEF</typ:Code>
<typ:Desc>Failed</typ:Desc>
<typ:SM>
<typ:From>ABC</typ:From>
<typ:To>DEF</typ:To>
<typ:Number>2.0</typ:Number>
</typ:SM>
</typ:T>

</typ:Ts>
<typ:Stat>Success</typ:Stat>
<typ:MsgCode>XYZ</typ:MsgCode>
<typ:MsgDesc>XYZ description</typ:MsgDesc>
</tns:EReplyMsg>


I have xml file as above.


In my SQL Server 2012 database there is table EMsg In which all the columns are same as elements in above xml file i.e. there are columns like EId, AType, SLine, Code, Desc, From, To, Number, Stat, MsgCode, MsgDesc..


Now I want to write a stored procedure which accepts this xml file as input parameter and stores the element's values as it is in their respective columns.


Can anyone help me on this that how I can write this type of stored procedure?


Please help me as I've never tried this before.


Thanks in advance.


No comments:

Post a Comment