display XML from SQL field into SQL Table

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

General SQL Server Forums

New to SQL Server Programming

display XML from SQL field into SQL Table

Author

Topic

S_Nandra
Starting Member

3 Posts

Posted - 2009-03-06 : 05:51:18

I have the following xml stored in sql tables

select xml_data from matchtable

1
<Matches ReportId="001">
<Match Against="WBA" GameType="Fr" Date="27/2/2009">
<Players>
<Player PlayerId="001" PlayerName="Jack" Min="2" />
<Player PlayerId="002" PlayerName="Dean" Min="3" />
<Player PlayerId="003" PlayerName="Michael" Min="1" />
</Players>
</Match>
</Matches>

2
<Matches ReportId="002">
<Match Against="MC" GameType="CC" Date="28/2/2009">
<Players>
<Player PlayerId="001" PlayerName="Jack" Min="9.1" />
<Player PlayerId="002" PlayerName="Dean" Min="2" />
<Player PlayerId="003" PlayerName="Michael" Min="11" />
</Players>
</Match>
</Matches>

How would I output the data like this:

(27/2/2009) (27/2/2009)
PlayerName Min Min
--------------- --- ---
Jack 2 9.1
Dean 3 2
Michael 1 11

Thanks

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-09 : 10:29:59

--Put Minimum into 2 columns
Select Name, Min(Minimum) as Min1,
NULLIF(MAX(Minimum), MIN(Minimum)) AS Min2
from (
--Show XML column as rowset (Minimum is in one column)
Select
x.Player.value('@PlayerName[1]', 'varchar(100)') [Name],
x.Player.value('@Min[1]', 'varchar(100)') Minimum
From matchtable

CROSS APPLY xml_data.nodes('/Matches/Match/Players/Player') As x ( Player )
) as Tab

GROUP BY Name
ORDER BY Name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources