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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to create multivalue data in one record
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kabon
Starting Member

Indonesia
48 Posts

Posted - 02/17/2014 :  20:48:51  Show Profile  Reply with Quote
I have question for xml data. This is the data for example:
<row id="10000007" xml:space="preserve">
<c1>LD1717995892</c1>
<c2>20170728</c2>
<c2 m="1" s="2">20170828</c2>
<c3>3878418.98</c3>
<c3 m="1" s="2">3907507.13</c3>
<c29>ID0010011</c29>
</row>

I want to select c1,c2,c3 and c29 from the xml record. And the result like this:

LD1717995892 20170728::20170828 3878418.98::3907507.13 ID0010011

Can you help me for query?

stepson
Constraint Violating Yak Guru

Romania
278 Posts

Posted - 02/18/2014 :  01:19:54  Show Profile  Reply with Quote


declare @xml as xml
set @xml='<row id="10000007" xml:space="preserve">
<c1>LD1717995892</c1>
<c2>20170728</c2>
<c2 m="1" s="2">20170828</c2>
<c3>3878418.98</c3>
<c3 m="1" s="2">3907507.13</c3>
<c29>ID0010011</c29>
</row>'


--LD1717995892 20170728::20170828 3878418.98::3907507.13 ID0010011

select
	
	t.u.value('c1[1]' ,'varchar(30)') + ' ' +
	t.u.value('c2[1]','varchar(30)') +'::'+t.u.value('c2[2]','varchar(30)') + ' ' +
	t.u.value('c3[1]','varchar(30)') +'::'+ t.u.value('c3[2]','varchar(30)') + ' ' +
	t.u.value('c29[1]','varchar(30)') as [row]
	,t.u.value('c1[1]' ,'varchar(30)') as C1
	,t.u.value('c2[1]','varchar(30)') as C2
	,t.u.value('c2[2]','varchar(30)') as C22
	,t.u.value('c3[1]','varchar(30)') as c3
	,t.u.value('c3[2]','varchar(30)') as c33
	,t.u.value('c29[1]','varchar(30)') as c29
from @xml.nodes('row') t(u)





sabinWeb MCP
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/18/2014 :  13:22:34  Show Profile  Reply with Quote
Can there be more than 2 c2 and c3 nodes?
if yes use below

declare @x xml='<row id="10000007" xml:space="preserve">
<c1>LD1717995892</c1>
<c2>20170728</c2>
<c2 m="1" s="2">20170828</c2>
<c2 m="2" s="3">2876897</c2>
<c3>3878418.98</c3>
<c3 m="1" s="2">3907507.13</c3>
<c3 m="2" s="2">131233.23</c3>
<c3 m="4" s="3">342554.16</c3>
<c3 m="5" s="3">2423545.23</c3>
<c29>ID0010011</c29>
</row>'

select m.n.value('c1[1]','varchar(50)') as c1,
replace(cast(m.n.query('data(c2)') as varchar(max)),' ','::') as c2,
replace(cast(m.n.query('data(c2)') as varchar(max)),' ','::') as c3,
m.n.value('c29[1]','varchar(50)') as c29
from @x.nodes('/row')m(n)


output
-----------------------------------------------------------------------------------------------
c1	        c2	                        c3	                        c29
-----------------------------------------------------------------------------------------------
LD1717995892	20170728::20170828::2876897	20170728::20170828::2876897	ID0010011




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

kabon
Starting Member

Indonesia
48 Posts

Posted - 02/26/2014 :  21:25:09  Show Profile  Reply with Quote
thank you visakh.

Can you help me to create procedure from your answer that? because i must use it to many record not just only one record?

so i can call the procedure easily to get the select i want.
please help me
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.08 seconds. Powered By: Snitz Forums 2000