Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Using namespaces in XML queries

If your xml data contains a namespace, you also need to query the data using the same xml namespace.

DECLARE @Sample TABLE
        (
                rowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
                [xml] XML
        )

INSERT  @Sample
SELECT  '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value1</value></stringList>' UNION ALL
SELECT  NULL UNION ALL
SELECT  '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value5</value><value>value2</value></stringList>' UNION ALL
SELECT  '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value4</value><value>value1</value><value>value5</value><value>value2</value></stringList>'

SELECT  *
FROM    @Sample

;WITH XMLNAMESPACES('http://schemas.myCompany.com/myProject/[app]/stringList/1.0' AS theYak)
SELECT DISTINCT t.c.value('.', 'VARCHAR(20)')
FROM            @Sample AS s
CROSS APPLY     s.xml.nodes('/theYak:stringList/theYak:value') AS t(c)

Print | posted on Thursday, November 06, 2008 10:36 AM | Filed Under [ SQL Server 2008 SQL Server 2005 ]

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET