Wednesday, May 6, 2015

How to read and write xml in SQl Server?

DECLARE @MyXML XML
SET @MyXML = '<BharatKoshPayment Version="1.0" DepartmentCode="MYDEPARTMENT">
<Submit>
<Order OrderCode="EN02321021" InstallationId="123456">
<Description> 100 Employment News from MYDEPARTMENTWebshop </Description>
<Amount value="2600" CurrencyCode="INR" exponent="0"/>
<OrderContent></OrderContent>
<PaymentMethodMask>
<IncludeCode>OnLine</IncludeCode>
</PaymentMethodMask>
<Shopper>
<ShopperEmailAddress> Raina@myprovider.in </ShopperEmailAddress>
</Shopper>
<ShippingAddress>
<Address>
<FirstName>Raina</FirstName>
<LastName>Shopper</LastName>
<Address1>Shopperstreet</Address1>
<Address2>Shopperaddress2</Address2>
<Address3>Shopperaddress3</Address3>
<PostalCode>1234</PostalCode>
<City>Shoppercity</City>
<StateRegion>District</StateRegion>
<State>ShopperState</State>
<CountryCode>IN</CountryCode>
<MobileNumber>0123456789</MobileNumber>
</Address>
</ShippingAddress>
<BillingAddress>
<Address>
<FirstName>Anant</FirstName>
<LastName>Shopper</LastName>
<Address1>Shopperstreet</Address1>
<Address2>Shopperaddress2</Address2>
<Address3>Shopperaddress3</Address3>
<PostalCode>1234</PostalCode>
<City>Shoppercity</City>
<StateRegion>District</StateRegion>
<State>Shopperregion</State>
<CountryCode>IN</CountryCode>
<MobileNumber>0123456789</MobileNumber>
</Address>
</BillingAddress>
</Order>
</Submit>
</BharatKoshPayment>
'
declare @a varchar(100),@b varchar(100)
SELECT
@a=a.c.value('Order[1]/@OrderCode','varchar(100)'),
@b=a.c.value('Order[1]/BillingAddress[1]/Address[1]/Address1[1]','varchar(100)')
FROM @MyXML.nodes('/BharatKoshPayment/Submit') a(c)

select @a,@b

No comments:

Post a Comment