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)
 getting XML data from table column
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mrekoj
Starting Member

2 Posts

Posted - 06/10/2013 :  23:55:16  Show Profile  Reply with Quote
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

Romania
545 Posts

Posted - 06/11/2013 :  01:14:23  Show Profile  Reply with Quote
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 - 06/11/2013 :  23:50:05  Show Profile  Reply with Quote
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

Romania
545 Posts

Posted - 06/12/2013 :  01:47:43  Show Profile  Reply with Quote
with welcome

Sabin

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
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.15 seconds. Powered By: Snitz Forums 2000