SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 XML data insertion in SQL table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vijays3
Constraint Violating Yak Guru

India
338 Posts

Posted - 09/09/2013 :  10:54:50  Show Profile  Reply with Quote


Hi All,

I want XML data to be inserted int SQL table but 
could not figure out. #Currency is my table with 
assocaite columns and  @XMLCurrency is a variable 
which holds XML string.Please advise me how can I 
insert this XML data to my table.        


Create table #Currency (CurrencyId int ,ISOCode nvarchar(10),ISONumbricCOde int,ISOName nvarchar(50), IsEnabledForMPV int default 0)


Declare @XMLCurrency         nvarchar(max)

Set @XMLCurrency='<R><T><A>0</A><B>USD</B><C>840</C><D>US Dollar</D></T></R>'


Value 840 should insert into column ISONumbricCOde .
value USD should be insert into ISOCode column.
value 0 should insert into column CurrencyId.
values US Dollar should insert into column ISOName .

Kindly please suggest me on this .


Vijay is here to learn something from you guys.

Edited by - vijays3 on 09/09/2013 11:34:21

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 09/09/2013 :  11:47:01  Show Profile  Reply with Quote
INSERT INTO #Currency (ISONumbricCOde,ISOCode,CurrencyId,ISOName)
SELECT
	c2.value('C[1]','INT') AS ISONumbricCOde,
	c2.value('B[1]','VARCHAR(8)') AS ISOCode,
	c2.value('A[1]','INT') AS CurrencyId,
	c2.value('D[1]','VARCHAR(32)') AS ISOName 
FROM
	(SELECT CAST(@XMLCurrency AS XML)) X(c1)
	CROSS APPLY c1.nodes('/R/T')T(c2);
Go to Top of Page

vijays3
Constraint Violating Yak Guru

India
338 Posts

Posted - 09/10/2013 :  06:20:41  Show Profile  Reply with Quote
Thanks James It worked like magic.:)

Vijay is here to learn something from you guys.

Edited by - vijays3 on 09/10/2013 06:21:00
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000