SQL 2005 XML XQuery: Ignore Namespaces

less than 1 minute read

A dba mentioned to change XML schema of the xml being save to the database, not to include namespaces….well why not just ignore them.

Tested on: SQL 2005.

WORKS WITHOUT NAMESPACE

DECLARE @xmlDoc xml
SET @xmlDoc = ' <authors xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<child> <id>77696c646f6e65</id> <lname>Barrass</lname> <fname>Max</fname>
</child> </authors>'

select @xmlDoc.query('data(/authors/child/id)') as UserID

NAMESPACE DOES NOT WORK


DECLARE @xmlDoc xml
SET @xmlDoc = ' <authors xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<child xmlns="http://maxbarrass.spaces.live.com">
<id>77696c646f6e65</id> <lname>Barrass</lname> <fname>Max</fname> </child>
</authors>'

select @xmlDoc.query('data(/authors/child/id)') as UserID

WORKS WITH NAMESPACE

DECLARE @xmlDoc xml
SET @xmlDoc = ' <authors xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<child xmlns="http://maxbarrass.spaces.live.com">
<id>77696c646f6e65</id> <lname>Barrass</lname> <fname>Max</fname> </child>
</authors>'

select @xmlDoc.query('data(/*:authors/*:child/*:id)') as UserID

Leave a Comment