Post reply

Select XML data

  • July 21, 2008 at 9:31 pm

    #186733

    By way of example: I have a table called Table1. It has one column called Column1 of XML data type.

    The single record looks as follows: (NOTE: for purposes of the posting I have removed the ">".

    <Webstore xmlns="urn:WebstoreDeploymentSchema.xsd"

    <DatabaseTemplates

    <DatabaseTemplate Name="CoreCatalogDBDistributionDatabaseTemplate"

    </DatabaseTemplate

    </DatabaseTemplates

    </Webstore

    How can I write a query where the result set will be "CoreCatalogDBDistributionDatabaseTemplate". The attribute Name from the Element Database Template.

  • July 22, 2008 at 1:45 am

    #846196

    WITH XMLNAMESPACES ('urn:WebstoreDeploymentSchema.xsd' AS ns)

    SELECT r.value('@Name','VARCHAR(50)') AS 'Element Database Template'

    FROM Table1

    CROSS APPLY Column1.nodes('/ns:Webstore/ns:DatabaseTemplates/ns:DatabaseTemplate') AS x(r)

    ____________________________________________________

    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

  • July 22, 2008 at 9:29 am

    #846479

    Yahoo!! Thanks Mark!

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

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