Hopefully a Really Simple XML Shredding Question

  • 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 1 week, 6 days ago by  bvaljalo-1000038. Reason: clarification
  • 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
  • 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