Solved

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

Posted on 2011-09-29
5
740 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
  • 3
  • 2
5 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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 59

Accepted Solution

by:
Kevin Cross earned 500 total points
Comment Utility
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
Comment Utility
That worked!  You are a superstar! Thank you very much for your assistance.
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
You are most welcome!
Best regards and happy coding,

Kevin
0
 

Author Comment

by:adrian78
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now