SQL 2005 XML XQuery: Ignore Namespaces
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