Hopefully a Really Simple XML Shredding Question

Question

Hopefully a Really Simple XML Shredding Question

bvaljalo-1000038

Right there with Babe

Points: 760
More actions
April 1, 2021 at 12:13 am

#3864190
Consider the following:
```
DECLARE @docHandle int; 
DECLARE @xmlDocument xml 
set @xmldocument = N'
<pages>
  <page>
    <page_id>493</page_id>
    <properties>
      <property key="title">Showing Your Home that is for Sale - Home Selling Tips </property>
    </properties>
    <content>
      <html> This Is Your Marketing Page</html>
    </content>
  </page>
</pages>'

EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;

SELECT * FROM OPENXML(@docHandle, N'/pages/page', 2) 
WITH (page_id varchar(1000), content varchar(max), title varchar(1000) 'properties/property/@key');
```
So ... this all works 'as expected', but is not what I need. Rather than having the last field title populated with the value 'title', what I actually need returned is the value 'Showing Your Home that is for Sale - Home Selling Tips' instead. How do I write my column definition to accomplish that? If I have to do it as a separate query that's fine, as long as I have page_id and title returned in the other query.
I should also say there will other property records, but one and only one where key = 'title'
I've spent way too long on this, your help is greatly appreciated!
- This topic was modified 3 years, 7 months ago by bvaljalo-1000038. Reason: clarification

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

Try this

SELECT n.x.value('(../../page_id)[1]','varchar(1000)') page_id,
       n.x.value('(../../content/html)[1]','varchar(max)') content,
       n.x.value('.','varchar(1000)') title
FROM @xmlDocument.nodes('/pages/page/properties/property[@key="title"]') n(x);

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

bvaljalo-1000038 Right there with Babe Points: 760 More actions · Answer 2

Thanks so much!

I actually had to get this done a couple hours ago, so I dusted off my old RegEx skills and reformatted the properties node to look like a way that I understood the SS nomenclature for.

Like

</properties>

I actually had like 4 of these 'key' nodes to get, but I just gave one figuring it would be the same for all of them.

Thanks again