Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

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

Constraint Violating Yak Guru

354 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

3873 Posts

Posted - 09/09/2013 :  11:47:01  Show Profile  Reply with Quote
INSERT INTO #Currency (ISONumbricCOde,ISOCode,CurrencyId,ISOName)
	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 
	(SELECT CAST(@XMLCurrency AS XML)) X(c1)
	CROSS APPLY c1.nodes('/R/T')T(c2);
Go to Top of Page

Constraint Violating Yak Guru

354 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  
 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.02 seconds. Powered By: Snitz Forums 2000