Selecting attributes and tags from XML

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

SQL Server 2005 Forums

Transact-SQL (2005)

Selecting attributes and tags from XML

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2008-02-13 : 07:52:22

Hi,

I've got an XML structure which looks a bit like this:


<location>
  <document id="1" sortOrder="1">
    <title></title>
    <author></author>
    <abstract></abstract>
  </document>
  <document>..</document>
  <document>..</document>
</location>

I need to read each document as a row into a temp table from which I can then run other queries. However, I'm a bit stuck on the syntax of how to get both the attributes and the tag data from each document object.

This is how I'd grab the tags and put them into a temp table:


select 
  title,
  author,
  abstract
into #tempData from openxml (@idoc, '/location/document/', 2)  
with 
  (
    title varchar(4000),
    author varchar(4000),
    abstract varchar(4000)
   )

And I know that to get attributes you replace the "2" with a "1" in the openxml call. But how can I do both together?

Cheers,
Matt

mattt
Posting Yak Master

194 Posts

Posted - 2008-02-13 : 10:11:28

It turns out that the answer is to put in a "3" as a flag instead of 1 or 2. I thought this worth recording since this fact is documented virtually nowhere, including in MS own official documentation which claims that 1, 2 and 8 are the only valid flag values.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources