query xml datatype - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

query xml datatype

Author

Topic

jingo_man
Starting Member

1 Post

Posted - 2007-03-12 : 11:34:45

i have an xml in the following format:

<Players>
<Player N="Player 1" S="p1" Ht="H" Hv="16" Sx="M" />
<Player N="Player 2" S="p2" Ht="H" Hv="0" Sx="M" />
<Player N="Player 3" S="p3" Ht="H" Hv="12" Sx="M" />
<Player N="Player n" S="pn" Ht="H" Hv="18" Sx="M" />
</Players>

i have imported this into an xmldata datatype field of a table, but from here i am stuck as how to query this.
looking at examples in my research, i only need a few simple examples of how to do it in this instance, and should be able to progress from there, but need help with the baby steps!! and there is more to the structure that the above, but think i will get by learning the basics.

regards,

jingo_man

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-12 : 12:30:02

http://msdn2.microsoft.com/en-us/library/ms189075.aspx

EDIT: fixed link (posted wrong one)

www.elsasoft.org

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-12 : 13:10:47

Here are a few examples to get you started

create table #XMLDemo
(
DemoID int,
XMLCol xml
)
insert #XMLDemo 
select 1, '<Players>
<Player N="Player 1" S="p1" Ht="H" Hv="16" Sx="M" /> 
<Player N="Player 2" S="p2" Ht="H" Hv="0" Sx="M" /> 
<Player N="Player 3" S="p3" Ht="H" Hv="12" Sx="M" /> 
<Player N="Player n" S="pn" Ht="H" Hv="18" Sx="M" /> 
</Players>' union all
select 2, '<Players>
<Player N="Player 1" S="p1" Ht="H" Hv="16" Sx="M" /> 
<Player N="Player n" S="pn" Ht="H" Hv="18" Sx="M" /> 
</Players>' union all
select 3, '<Players>
<Player N="Player 1" S="p1" Ht="H" Hv="12" Sx="M" /> 
<Player N="Player 2" S="p2" Ht="H" Hv="0" Sx="M" /> 
</Players>'

select * from #XMLDemo

select DemoID, XMLCol.value('(Players/Player[@N="Player 3"]/@Hv)[1]', 'int') as Hv 
from #XMLDemo

select DemoID, XMLCol.query('Players/Player[@N="Player 2"]') as Player 
from #XMLDemo

select DemoID 
from #XMLDemo
where XMLCol.exist('(Players/Player[@Hv="12"])') = 1

go
drop table #XMLDemo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources