Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vijays3
Constraint Violating Yak Guru
354 Posts |
 Posted - 2013-09-09 : 10:54:50
|
| [code]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>'[/code]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. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-09-09 : 11:47:01
|
| [code]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);[/code] |
 |
|
|
vijays3
Constraint Violating Yak Guru
354 Posts |
Posted - 2013-09-10 : 06:20:41
|
| Thanks James It worked like magic.:)Vijay is here to learn something from you guys. |
|
|
|
|
|
|
|
|
XML data insertion in SQL table