How to create multivalue data in one record

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to create multivalue data in one record

Author

Topic

kabon
Starting Member

48 Posts

Posted - 2014-02-17 : 20:48:51

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

545 Posts

Posted - 2014-02-18 : 01:19:54

[code]

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)

[/code]

sabinWeb MCP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-18 : 13:22:34

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

kabon
Starting Member

48 Posts

Posted - 2014-02-26 : 21:25:09

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources