locked
Query Xml without Namespace prefix fails ??? RRS feed

  • Question

  • I am having a problem querying Xml data in SQL SERVER 2005 if there is an xmlnamespace that does not have a prefix.
    The query ends up returning a NULL field. The minute I add a prefix then the query works.
    How can I query the Xml data that does not have an Xml prefixed namespace.

    Here are the samples

    declare @xml xml
    set @xml = 
    '<Response xmlns:a="http://tempuri.org"> 
    <Data>hello world</Data>
    </Response>'
    SELECT @xml.query('/Response/Data')

    the above SQL will work because the xmlnamespace has an explicit prefix
    if the prefix is removed, then the query will return NULL with the below SQL

    declare @xml xml
    set @xml = 
    '<Response xmlns="http://tempuri.org"> 
    <Data>hello world</Data>
    </Response>'
    SELECT @xml.query('/Response/Data')

    So what I need to achieve is the Xml query to work with Xml fragments where namespaces may or may not have a prefix


    Thanks
    Wednesday, June 17, 2009 2:46 AM

All replies

  • You need to refer to the namespace and prefix combination in the XQuery prolog 
     
    declare @xml xml
    set @xml =
    '<Response xmlns:a="http://tempuri.org">
    <Data>hello world</Data>
    </Response>'
    SELECT @xml.query('declare namespace a="
    http://tempuri.org";
    /a:Response/a:Data'
     
    or
     
    declare @xml xml
    set @xml =
    '<Response xmlns:a="http://tempuri.org">
    <Data>hello world</Data>
    </Response>'
    SELECT @xml.query('declare default element namespace "
    http://tempuri.org";
    /Response/Data'
     
    or use WITH XML NAMESPACES(...)
     
    Cheers,
    Bob Beauchemin
    SQLskills
    Wednesday, June 17, 2009 3:08 AM
  • This works in both cases, though it seems to add some annotations that I did not expect in the second case:

    	declare @xml xml
    	set @xml = 
    	'<Response xmlns="http://tempuri.org"> 
    	<Data>hello world</Data>
    	</Response>'
    	SELECT @xml.query('/*:Response/*:Data')
    


    RBarryYoung
    • Proposed as answer by RBarryYoung Wednesday, July 22, 2009 9:31 PM
    Wednesday, June 17, 2009 11:42 PM