April 1, 2021 at 12:13 am
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!
April 1, 2021 at 7:30 am
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/61537April 1, 2021 at 7:39 am
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>
<property title = "Showing Your Home that is for Sale - Home Selling Tips" />
</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
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy