Peter Larsson Blog

Patron Saint of Lost Yaks

Using namespaces in XML queries

by Peter Larsson in  ,

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)