Select XML data
-
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.
-
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
-
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