Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 807
  • Last Modified:

Querying XML using OPENXML WITH - mixed attribute-centric and element-centric

Hello,

I have an XML structure that is mixed - both attribute-centric and element-centric.  For example:

<stats>
<year>2011</year>
<stat num="33" type="days_attended"/>
<stat num="12" type="referrals"/>
</stats>

I'm using element-centric to grab the data.  For example:

SELECT year,days_attended,referrals
FROM OPENXML (@mydata, //xml-tree/stats',2)
WITH
(
year int 'year',
days_attended int ???,
referrals int ???
)

How would I grab the value of "num" based upon the type = "days_attended" for example.
I know I can grab num using something like:

num int 'stat/@num'

...But how would I know which num it is.  I want to be able to grab that num value and call the column by the type value (days_attended for example).

Thanks in advance!
0
adrian78
Asked:
adrian78
  • 3
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
I will have to check on the OPENXML version to confirm it is the similar, but here is how I would do this using the XML data type.
DECLARE @mydata XML
SET @mydata = '<stats>
<year>2011</year>
<stat num="33" type="days_attended"/>
<stat num="12" type="referrals"/>
</stats>'
;

SELECT x.s.value('year[1]', 'INT') [year]
     , x.s.value('stat[@type="days_attended"][1]/@num', 'INT') [days_attended]
     , x.s.value('stat[@type="referrals"][1]/@num', 'INT') [referrals]
FROM @mydata.nodes('//stats') x(s)
;

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Okay, confirmed. Here is the OPENXML version.
-- http://technet.microsoft.com/en-us/library/ms186918.aspx
DECLARE @mydata INT
DECLARE @xml VARCHAR(8000)
SET @xml = '<stats>
<year>2011</year>
<stat num="33" type="days_attended"/>
<stat num="12" type="referrals"/>
</stats>'
;

EXEC sp_xml_preparedocument @mydata OUTPUT, @xml

SELECT [year], days_attended, referrals
FROM OPENXML(@mydata, '//stats', 2)
WITH
(
year int 'year',
days_attended int 'stat[@type="days_attended"]/@num',
referrals int 'stat[@type="referrals"]/@num'
)

EXEC sp_xml_removedocument @mydata

Open in new window

0
 
adrian78Author Commented:
That worked!  You are a superstar! Thank you very much for your assistance.
0
 
Kevin CrossChief Technology OfficerCommented:
You are most welcome!
Best regards and happy coding,

Kevin
0
 
adrian78Author Commented:
Hey Kevin,

I ran into another issue!  I've posted this under a new question - your help would be greatly appreciated!

Thanks,
Adrian
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now