Trying to list declared XML-namespaces

Question

We have a number of applications accessing our APIs by providing XML data. At a certain point we decided to use xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" for incoming NULL-values to be able to distinguish empty values from NULL values in a uniform way.

Because the transition to xsi:nil is still ongoing, I want to be able to tell whether or not the xsi-namespace is declared as an indicator of whether or not the calling application would use xsi:nil="true" for NULL-values.

I tried

DECLARE @SomeXML xml = N'<ROOT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><blubb><blah xsi:nil="true"/></blubb></ROOT>';

SELECT  @SomeXML.exist('declare namespace xsi="http://www.w3.org/2001/XMLSchema-instance"; //xsi:*, //@xsi:*');

but this will only work if the namespace is actually referred to in the XML-document. A query like //@xmlns:* leads to the error

Msg 2229, Level 16, State 1, Line 6
XQuery [query()]: The name "xmlns" does not denote a namespace.

while a query for //@*:xsi just returns nothing at all.

Is there any way to determine the declared XML-namespaces in SQL Server 2016?

Answer 1

I learned from this thread, that there is a rather old way to enumerate namespaces utilizing OPENXML:

DECLARE @SomeXML xml = N'<ROOT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.w3.org/2001/XMLSchema-instance"><blubb xmlns:xsiu="http://www.w3.org/2001/XMLSchema-instance"><blah xsi:nil="true"/></blubb></ROOT>';

DECLARE @hDoc int;

EXEC sys.sp_xml_preparedocument
    @hDoc OUTPUT,
    @SomeXML;

IF EXISTS ( SELECT  namespace = NULLIF(XmlnsAttribute.localname, 'xmlns'),
                    namespace_uri = XmlnsValue.text
            FROM    OPENXML( @hDoc, '//*' ) XmlnsAttribute
            INNER JOIN OPENXML( @hDoc, '//*' ) XmlnsValue ON XmlnsValue.parentid = XmlnsAttribute.id
            WHERE   XmlnsAttribute.prefix = 'xmlns'
                    AND XmlnsValue.nodetype = 3 /*text*/
                    AND CAST(XmlnsValue.text AS nvarchar(MAX)) = N'http://www.w3.org/2001/XMLSchema-instance' )
    PRINT 'Has http://www.w3.org/2001/XMLSchema-instance namespace';
ELSE
    PRINT 'Does not have http://www.w3.org/2001/XMLSchema-instance namespace';

EXEC sys.sp_xml_removedocument
    @hDoc;

I am uncertain how elegant this is, as text column is of type ntext, and the calls to sys.sp_xml_preparedocument and sys.sp_xml_removedocument mean, that you need to pay a bit more attention, when you include this into other queries. Probably the worst draw-back is, that you need to do this RBAR.

Still this is a solution without casting xml to varchar, so it should be difficult to spoof.

Listing all namespaces:

DECLARE @SomeXML xml = N'<ROOT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.w3.org/2001/XMLSchema-instance"><blubb xmlns:xsiu="http://www.w3.org/2001/XMLSchema-instance"><blah xsi:nil="true"/></blubb></ROOT>';

DECLARE @hDoc int;

EXEC sys.sp_xml_preparedocument
    @hDoc OUTPUT,
    @SomeXML;

-- All registered namespaces
WITH    XmlNodes
          AS (SELECT    OX.id,
                        OX.parentid,
                        OX.nodetype,
                        OX.localname,
                        OX.prefix,
                        OX.text
              FROM      OPENXML( @hDoc, '//*' ) OX)
    SELECT  namespace = NULLIF(XmlnsAttribute.localname, 'xmlns'),
            namespace_uri = XmlnsValue.text
    FROM    XmlNodes XmlnsAttribute
    INNER JOIN XmlNodes XmlnsValue ON XmlnsValue.parentid = XmlnsAttribute.id
    WHERE   XmlnsAttribute.prefix = 'xmlns'
            AND XmlnsValue.nodetype = 3
 /*text*/;

-- All registered namespaces with scope
WITH    XmlNodes
          AS (SELECT    OX.id,
                        OX.parentid,
                        OX.nodetype,
                        OX.localname,
                        OX.prefix,
                        OX.text
              FROM      OPENXML( @hDoc, '//*' ) OX),
        XmlNodesWithPath
          AS (SELECT    XN.id,
                        path = CAST(N'/' + ISNULL(XN.prefix + N':', N'') + XN.localname AS nvarchar(MAX))
              FROM      XmlNodes XN
              WHERE     XN.parentid IS NULL
              UNION ALL
              SELECT    XN.id,
                        path = XNWP.path + N'/' + ISNULL(XN.prefix + N':', N'') + XN.localname
              FROM      XmlNodesWithPath XNWP
              INNER JOIN XmlNodes XN ON XN.parentid = XNWP.id
                                        AND XN.nodetype = 1)
    SELECT  scope = Scope.path,
            namespace = NULLIF(XmlnsAttribute.localname, 'xmlns'),
            namespace_uri = XmlnsValue.text
    FROM    XmlNodesWithPath Scope
    INNER JOIN XmlNodes XmlnsAttribute ON XmlnsAttribute.parentid = Scope.id
    INNER JOIN XmlNodes XmlnsValue ON XmlnsValue.parentid = XmlnsAttribute.id
    WHERE   XmlnsAttribute.prefix = 'xmlns'
            AND XmlnsValue.nodetype = 3
 /*text*/;

EXEC sys.sp_xml_removedocument
    @hDoc;

Answer 2

I'm not sure there's going to be anything better than converting to nvarchar and doing a CHARINDEX on the URI. I think you're unlikely to get false positives but it doesn't feel right.

DECLARE @SomeXML xml = N'<ROOT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><blubb><blah xsi:nil="true"/></blubb></ROOT>'

select CHARINDEX(N'"http://www.w3.org/2001/XMLSchema-instance"',CONVERT(nvarchar(max), @SomeXML))

(We search just on the URI so that we pick it up even if different prefixes are used within the document)

The problem is that most XML tools assume that each "context" (such as an XML document, an XPath expression, etc) introduces whichever namespaces are relevant to it and so don't need a mechanism to explore namespace declarations inside other "contexts".