CODEWITHSUNDEEP
sql-serverxmlsql-server-2008xpathxml-namespaces
Pat
Pat

Reputation: 3

SQL Server Xml query with undeclared prefix

I have a variable in SQL Server 2008 that contains a part of a XML column and I am having trouble querying it. The XML can contain a prefix "ns0" or not. I want to retrieve [from] from the xml. The query I tried was this:

DECLARE @Params XML
SET @Params = '<filter>
                      <ns0:from>2016-09-19</ns0:from>
                      <ns0:to>2017-01-01<ns0:to>
               </filter>';
SELECT @Params.value('(/*:filter/*:from)[1]', 'Varchar(max)') AS [from]

I don't have a XML header to declare any namespace. When I try to execute this, I got this error:

 XML parsing: line 1, character 10, undeclared prefix

But when I try this, everything works fine:

SET @Params = '<filter>
                    <from>2016-09-19<from>
                    <to>2017-01-01<to>
               </filter>
SELECT @Params.value('(/*:filter/*:from)[1]', 'Varchar(max)') AS [from]

How can I retrieve [from] using an XPath query given the above xml example with a prefix or without prefix?

Upvotes: 0

Views: 3544

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67321

Your example is invalid in two ways:

  1. It is not allowed to have a namespace prefix without a corresponding namespace declaration.
  2. Your closing tags do not include the / everywhere...

This is an ugly hack, but you might try this:

DECLARE @Params XML
SET @Params = REPLACE('<filter>
                      <ns0:from>2016-09-19</ns0:from>
                      <ns0:to>2017-01-01</ns0:to>
               </filter>','ns0:','');
SELECT @Params.value('(/*:filter/*:from)[1]', 'date') AS [from];

If you do not know all namespace prefixes in advance this will get really tricky...

Upvotes: 0

Related Questions