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)
 getting XML data from table column

Author  Topic 

mrekoj
Starting Member

2 Posts

Posted - 2013-06-10 : 23:55:16
Hi,
I have a table that stores XML data.
The examples found are mainly used on 1 set of XML data, since I'm breaking up the xml in the table column, I will have multiple set of xml data.
May I know how do I retrieve the data without using cursor ?


my data


MsgID Data
---------------------------------------------------
1 <event>
<msg langID = "1">
<title> hello </title>
<main> body </title>
</msg>
<msg langID = "2">
<title> hola</title>
<main> body </title>
</msg>

2 <event>
<msg langID = "1">
<title> msg2head </title>
<main> msg2body </title>
</msg>
<msg langID = "2">
<title> msg2headinChinese </title>
<main> msg2bodyInChinese </title>
</msg>





The result i wanted


MsgID LangueID Title Main
---------------------------------------------------
1 1 hello body
1 2 hola body
2 1 msg2head msg2body
2 2 msg2headinChinese msg2bodyInChinese



additional info
the number of langID per xml in the data is not fixed.

Thanks in advance :)

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-06-11 : 01:14:23
your xml sample , i guess , is not correct
end tag does not match with start tag

1-<event> , does not close
2-<main> body </title> = <main> tag close with <title> ?

those are correct?

for test , I change to something like this :

<event>
<msg langID = "1">
<title> hello </title>
<main> body </main>
</msg>
<msg langID = "2">
<title> hola</title>
<main> body </main>
</msg>
</event>


here is my solution


select
xmlTable.[MsgID] as [MsgID]
,t.u.value('@langID[1]','nvarchar(30)') as [langID]
,t.u.value('title[1]' ,'nvarchar(30)') as [title]
,t.u.value('main[1]','nvarchar(30)') as [main]
from xmlTable
cross apply [xmlTable].Data.nodes('event/msg') as t(u)





Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

mrekoj
Starting Member

2 Posts

Posted - 2013-06-11 : 23:50:05
Sorry for the mistake in my xml.
was typing it in a rush before I left office yesterday.

you got what I meant.
Just tested your sql, it's working perfectly, thanks !!
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-06-12 : 01:47:43
with welcome

Sabin

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page
   

- Advertisement -