Get XML namespace text via SQL query

You can try something along this:

DECLARE @xml XML=
N'<Document xmlns="urn:iso:std:iso:20022:some:test:xmlns">
  <Testnode>a</Testnode>
</Document>';

--The XQuery-function namespace-uri() takes a singleton and returns its namespace uri

SELECT @xml.value('namespace-uri((/*:Document)[1])','nvarchar(max)');

As the <Document> element is living within the default namespace itself, we would have to know the namespace in advance in order to declare it. But - luckily - we can use the wildcard with *:.

Another option - one of the rare cases - is the usage of the outdated FROM OPENXML:

Try this:

DECLARE @xml XML=
N'<Document xmlns="urn:iso:std:iso:20022:some:test:xmlns">
  <Testnode>a</Testnode>
</Document>';

DECLARE @docHandle INT;
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xml;  
SELECT * FROM OPENXML (@docHandle, '/*',1);  
EXEC sp_xml_removedocument @docHandle; 

This returns the complete XML with a lot of meta-data:

+----+----------+----------+-----------+--------+---------------------------------------+----------+------+---------------------------------------+
| id | parentid | nodetype | localname | prefix | namespaceuri                          | datatype | prev | text                                  |
+----+----------+----------+-----------+--------+---------------------------------------+----------+------+---------------------------------------+
| 0  | NULL     | 1        | Document  | NULL   | urn:iso:std:iso:20022:some:test:xmlns | NULL     | NULL | NULL                                  |
+----+----------+----------+-----------+--------+---------------------------------------+----------+------+---------------------------------------+
| 2  | 0        | 2        | xmlns     | xmlns  | NULL                                  | NULL     | NULL | NULL                                  |
+----+----------+----------+-----------+--------+---------------------------------------+----------+------+---------------------------------------+
| 4  | 2        | 3        | #text     | NULL   | NULL                                  | NULL     | NULL | urn:iso:std:iso:20022:some:test:xmlns |
+----+----------+----------+-----------+--------+---------------------------------------+----------+------+---------------------------------------+
| 3  | 0        | 1        | Testnode  | NULL   | urn:iso:std:iso:20022:some:test:xmlns | NULL     | NULL | NULL                                  |
+----+----------+----------+-----------+--------+---------------------------------------+----------+------+---------------------------------------+
| 5  | 3        | 3        | #text     | NULL   | NULL                                  | NULL     | NULL | a                                     |
+----+----------+----------+-----------+--------+---------------------------------------+----------+------+---------------------------------------+