Please start any new threads on our new site at http://forums.sqlteam.com. 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
 
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
 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
Aged Yak Warrior

Romania
545 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
52326 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  
 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.05 seconds. Powered By: Snitz Forums 2000