Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

XML View? Expand / Collapse
Author
Message
Posted Monday, May 20, 2013 8:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 19, 2013 9:07 AM
Points: 47, Visits: 84
If i have at table with a xml column datatype and want to create sql view which displays certain values from within xml nodes of ALL the table records (not just one) what is the best way to do this? All the examples I've seen take simply the value of one xml column of one records a produce a reuslt but what if I wanted to query over all the records and produce SUMS, group bys, ETC. Is the only way for me to creatre a sproc and loop through every record, insert them into a temp table, and then do my selects/aggregations? Or is there a better more efficient way?

thanks for any advice!
nick



Post #1454574
Posted Monday, May 20, 2013 8:59 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, September 18, 2014 2:53 AM
Points: 702, Visits: 2,178
Hi,

If you look at the details of this post it may help you. If I understand what you want....

http://www.sqlservercentral.com/Forums/Topic1438775-3077-1.aspx

Thanks,


Simon




MCSA: SQL Server 2012
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
Post #1454578
Posted Monday, May 20, 2013 9:12 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, September 18, 2014 2:53 AM
Points: 702, Visits: 2,178
Or.... here's a really simple example but should get you going....

/* Set up a table variable to hold the XML*/
DECLARE @table TABLE
(
RiskXML XML
)

/* Insert some very simple XML */
INSERT INTO @table
SELECT '<RISK>
<ID>1</ID>
<VALUE>20</VALUE>
</RISK>'
UNION ALL SELECT '<RISK>
<ID>2</ID>
<VALUE>20</VALUE>
</RISK>'
UNION ALL SELECT '<RISK>
<ID>3</ID>
<VALUE>20</VALUE>
</RISK>'

/* Prove it's in there! */
SELECT * FROM @table


/* This is how we can flatten (shred) it */
SELECT
r.risk.value('(ID)[1]', 'int') AS ID,
r.risk.value('(VALUE)[1]', 'int') AS Value
FROM
@table t
CROSS APPLY t.RiskXML.nodes('RISK') r(risk)

/* Simple aggregations on it */
SELECT
COUNT(r.risk.value('(ID)[1]', 'int')) AS [Count],
SUM(r.risk.value('(VALUE)[1]', 'int')) AS [Sum]
FROM
@table t
CROSS APPLY t.RiskXML.nodes('RISK') r(risk)





MCSA: SQL Server 2012
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
Post #1454580
Posted Monday, May 20, 2013 1:27 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 19, 2013 9:07 AM
Points: 47, Visits: 84
Thanks Simon! Much obliged. Think you gave me enough to get started.


Post #1454706
Posted Tuesday, May 21, 2013 2:08 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, September 18, 2014 2:53 AM
Points: 702, Visits: 2,178
Cool, glad it's helped. Post back if you have any problems.

Thanks,


Simon




MCSA: SQL Server 2012
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
Post #1454858
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse