Solved

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

Posted on 2011-09-29
5
780 Views
Last Modified: 2012-05-12
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
Comment
Question by:adrian78
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36890122
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
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36890145
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
 

Author Comment

by:adrian78
ID: 36893846
That worked!  You are a superstar! Thank you very much for your assistance.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36894284
You are most welcome!
Best regards and happy coding,

Kevin
0
 

Author Comment

by:adrian78
ID: 36898565
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL works but want to get the XML node data separately 11 54
Database Mail Profiles 1 51
SQL Query - Multiple match on field with no extras 7 35
grouping by date only 6 22
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question