How can I match the xmlns:* attributes with SQL?

Question

Question

Is there any way to use XPath which matches on the namespace axis in SQL Server? i.e. I'm aware that SQL does not natively support this axis; but is there any query which would be functionally similar which may work?

Context

I'm hoping to write code to debloat duplicate namespaces from my XML, leaving only those declarations which exist on the root element. I've seen various other solutions for this, but all are quite painful; so I investigated alternate solutions & in doing so realised that SQL does not support the namespace axis.

declare @demo xml = '
<hello:a xmlns:hello="test" xmlns:world="me">
    <hello:b>
        <world:c xmlns:world="me">demo</world:c>
        <hello:d xmlns:hello="test">demo</hello:d>
        <world:e xmlns:hello="test" xmlns:world="me">demo</world:e>
        <hello:f xmlns:hello="test" xmlns:world="me" world:demo=''x''>demo</hello:f>
    </hello:b>
</hello:a>
'

set @demo.modify('delete (/*//namespace::*)') 
--set @demo.modify('delete (/*//@*[not(namespace-uri() > "")])') --tried just in case xmlns is treated as an attribute in SQL; no joy :/

select @demo

Research

NB: There's a similar question asking how this is done in XSLT; but SQL-Server does not include the namespace:: axis. A list of the available axes in SQL is available here.

There are other ways to remove this bloat; but none are that straightforwards, and these posts are now quite dated, hence my researching alternate approaches:

a good overview of the various available methods: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2f7bdfbf-8e40-456b-84e8-195318649703/how-to-remove-namespaces-from-xml-tags-when-using-for-xml-option-with-xmlnamespaces?forum=transactsql&prof=required
a few answers on how to avoid this issue when generating the XML within SQL How do I remove redundant namespace in nested query when using FOR XML PATH

Current Solution

NB: Since I've been unable to find a straight forwards solution, my current method for removing these namespaces is as below. There are some risks with this (e.g. losing attributes on the root element, issues if the expected spaces are missing / other whitespace characters are used instead), but this is good enough for my purposes, if very hacky & non-generic.

declare @demo xml = '
<hello:a xmlns:hello="test" xmlns:world="me">
    <hello:b>
        <world:c xmlns:world="me">demo</world:c>
        <hello:d xmlns:hello="test">demo</hello:d>
        <world:e xmlns:hello="test" xmlns:world="me">demo</world:e>
        <hello:f xmlns:hello="test" xmlns:world="me" world:demo=''x''>demo</hello:f>
    </hello:b>
</hello:a>
'

;with xmlnamespaces('test' as hello, 'me' as world) 
select @demo = cast(
    '<hello:a xmlns:hello="test" xmlns:world="me">' 
    + replace(
        replace(
            cast(@demo.query('/*/*') as nvarchar(max))
            ,' xmlns:hello="test"'
            ,''
        ) 
        ,' xmlns:world="me"'
        ,''
    ) 
    + '</hello:a>'  
    as xml
)
select @demo

Good question, well put, it's just too bad the answer really does seem to be "you can't". The engine *really* wants namespaces to be constant and usable as metadata only, and entirely removing your view of them seems to be just one more way to achieve that. Namespace nodes are literally invisible to all XPath/XQuery constructs supported in T-SQL. (This would make removing them actually possible, by copying the rest with XQuery -- if the XQuery engine in SQL Server supported declaring functions, which it does not.) If you needed this fully reliably, I think a CLR function is the only way to go. — Jeroen Mostert, Aug 03 '18 at 14:30
I'll put this out there for completeness, even though it doesn't actually *help* this effort: that old warhorse `OPENXML` *can* access namespace nodes, so you can do (e.g.) `DECLARE @hdoc int; EXEC sp_xml_preparedocument @hdoc OUTPUT, @demo; SELECT * FROM OPENXML(@hdoc, '//*') WHERE prefix <> 'xmlns' or parentid = 0 EXEC sp_xml_removedocument @hdoc;` But, as I said, turning that back into XML again is either impossible or (if you accept cursors) very, very inconvenient/inefficient. — Jeroen Mostert, Aug 03 '18 at 14:32

score 1 · Accepted Answer · answered Aug 05 '18 at 15:28

SQL-Server's abilities to deal with XML namespaces is really - uhm - painful...

the only way I know to define namespaces just as you want them is FOR XML EXPLICIT (unless you want to walk the string-manipulation route...)

You can create the XML you want with:

SELECT 1      AS Tag
      ,NULL   AS Parent 
      ,'test' AS [hello:a!1!xmlns:hello]
      ,'me'   AS [hello:a!1!xmlns:world]
      ,NULL   AS [hello:b!2]
      ,NULL   AS [world:c!3]
      ,NULL   AS [hello:d!4]
      ,NULL   AS [world:e!5]
      ,NULL   AS [hello:f!6]
      ,NULL   AS [hello:f!6!world:demo]
UNION ALL
SELECT 2    
      ,1
      ,NULL
      ,NULL
      ,''
      ,NULL
      ,NULL
      ,NULL
      ,NULL
      ,NULL
UNION ALL
SELECT 3    
      ,2
      ,NULL
      ,NULL
      ,''
      ,'demo'
      ,NULL
      ,NULL
      ,NULL
      ,NULL
UNION ALL
SELECT 4    
      ,2
      ,NULL
      ,NULL
      ,''
      ,NULL
      ,'demo'
      ,NULL
      ,NULL
      ,NULL
UNION ALL
SELECT 5    
      ,2
      ,NULL
      ,NULL
      ,''
      ,NULL
      ,NULL
      ,'demo'
      ,NULL
      ,NULL
UNION ALL
SELECT 6    
      ,2
      ,NULL
      ,NULL
      ,''
      ,NULL
      ,NULL
      ,NULL
      ,'demo'
      ,'x'
FOR XML EXPLICIT;

The result

<hello:a xmlns:hello="test" xmlns:world="me">
  <hello:b>
    <world:c>demo</world:c>
    <hello:d>demo</hello:d>
    <world:e>demo</world:e>
    <hello:f world:demo="x">demo</hello:f>
  </hello:b>
</hello:a>

As Jeroen Mostert has pointed out in comments you might use outdated FROM OPEN XML like here:

declare @demo xml = 
'<hello:a xmlns:hello="test" xmlns:world="me">
    <hello:b>
        <world:c xmlns:world="me">demo</world:c>
        <hello:d xmlns:hello="test">demo</hello:d>
        <world:e xmlns:hello="test" xmlns:world="me">demo</world:e>
        <hello:f xmlns:hello="test" xmlns:world="me" world:demo=''x''>demo</hello:f>
    </hello:b>
</hello:a>';

DECLARE @hdoc int; 
EXEC sp_xml_preparedocument @hdoc OUTPUT, @demo;
SELECT * 
FROM OPENXML(@hdoc, '//*');
EXEC sp_xml_removedocument @hdoc;
GO

The result

+----+----------+----------+-----------+--------+--------------+----------+------+------+
| id | parentid | nodetype | localname | prefix | namespaceuri | datatype | prev | text |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 0  | NULL     | 1        | a         | hello  | test         | NULL     | NULL | NULL |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 2  | 0        | 2        | hello     | xmlns  | NULL         | NULL     | NULL | NULL |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 20 | 2        | 3        | #text     | NULL   | NULL         | NULL     | NULL | test |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 3  | 0        | 2        | world     | xmlns  | NULL         | NULL     | NULL | NULL |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 21 | 3        | 3        | #text     | NULL   | NULL         | NULL     | NULL | me   |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 4  | 0        | 1        | b         | hello  | test         | NULL     | NULL | NULL |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 5  | 4        | 1        | c         | world  | me           | NULL     | NULL | NULL |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 6  | 5        | 2        | world     | xmlns  | NULL         | NULL     | NULL | NULL |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 22 | 6        | 3        | #text     | NULL   | NULL         | NULL     | NULL | me   |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 7  | 5        | 3        | #text     | NULL   | NULL         | NULL     | NULL | demo |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 8  | 4        | 1        | d         | hello  | test         | NULL     | 5    | NULL |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 9  | 8        | 2        | hello     | xmlns  | NULL         | NULL     | NULL | NULL |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 23 | 9        | 3        | #text     | NULL   | NULL         | NULL     | NULL | test |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 10 | 8        | 3        | #text     | NULL   | NULL         | NULL     | NULL | demo |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 11 | 4        | 1        | e         | world  | me           | NULL     | 8    | NULL |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 12 | 11       | 2        | hello     | xmlns  | NULL         | NULL     | NULL | NULL |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 24 | 12       | 3        | #text     | NULL   | NULL         | NULL     | NULL | test |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 13 | 11       | 2        | world     | xmlns  | NULL         | NULL     | NULL | NULL |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 25 | 13       | 3        | #text     | NULL   | NULL         | NULL     | NULL | me   |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 14 | 11       | 3        | #text     | NULL   | NULL         | NULL     | NULL | demo |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 15 | 4        | 1        | f         | hello  | test         | NULL     | 11   | NULL |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 16 | 15       | 2        | hello     | xmlns  | NULL         | NULL     | NULL | NULL |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 26 | 16       | 3        | #text     | NULL   | NULL         | NULL     | NULL | test |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 17 | 15       | 2        | world     | xmlns  | NULL         | NULL     | NULL | NULL |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 27 | 17       | 3        | #text     | NULL   | NULL         | NULL     | NULL | me   |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 18 | 15       | 2        | demo      | world  | me           | NULL     | NULL | NULL |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 28 | 18       | 3        | #text     | NULL   | NULL         | NULL     | NULL | x    |
+----+----------+----------+-----------+--------+--------------+----------+------+------+
| 19 | 15       | 3        | #text     | NULL   | NULL         | NULL     | NULL | demo |
+----+----------+----------+-----------+--------+--------------+----------+------+------+

This table includes all the information you would need to create the statement above in a recursive CTE dynamically and use EXEC to create the XML from scratch.

with WHERE nodetype=1 you get the elements, with 2 the attributes...

But - to be honest - this is a huge effort...

If your XMLs are more complicated, nested, whatever, this will get really bad...

How can I match the xmlns:* attributes with SQL?

Question

Context

Research

Current Solution

1 Answers1