How do I remove redundant namespace in nested query when using FOR XML PATH
If I have understood correctly, you are referring to the behavior that you might see in a query like this:
DECLARE @Order TABLE (
OrderID INT,
OrderDate DATETIME)
DECLARE @OrderDetail TABLE (
OrderID INT,
ItemID VARCHAR(1),
ItemName VARCHAR(50),
Qty INT)
INSERT @Order
VALUES
(1, '2010-01-01'),
(2, '2010-01-02')
INSERT @OrderDetail
VALUES
(1, 'A', 'Drink', 5),
(1, 'B', 'Cup', 2),
(2, 'A', 'Drink', 2),
(2, 'C', 'Straw', 1),
(2, 'D', 'Napkin', 1)
;WITH XMLNAMESPACES('http://test.com/order' AS od)
SELECT
OrderID AS "@OrderID",
(SELECT
ItemID AS "@od:ItemID",
ItemName AS "data()"
FROM @OrderDetail
WHERE OrderID = o.OrderID
FOR XML PATH ('od.Item'), TYPE)
FROM @Order o
FOR XML PATH ('od.Order'), TYPE, ROOT('xml')
Which gives the following results:
<xml xmlns:od="http://test.com/order">
<od.Order OrderID="1">
<od.Item xmlns:od="http://test.com/order" od:ItemID="A">Drink</od.Item>
<od.Item xmlns:od="http://test.com/order" od:ItemID="B">Cup</od.Item>
</od.Order>
<od.Order OrderID="2">
<od.Item xmlns:od="http://test.com/order" od:ItemID="A">Drink</od.Item>
<od.Item xmlns:od="http://test.com/order" od:ItemID="C">Straw</od.Item>
<od.Item xmlns:od="http://test.com/order" od:ItemID="D">Napkin</od.Item>
</od.Order>
</xml>
As you said, the namespace is repeated in the results of the subqueries.
This behavior is a feature according to a conversation on devnetnewsgroup (website now defunct) although there is the option to vote on changing it.
My proposed solution is to revert back to FOR XML EXPLICIT:
SELECT
1 AS Tag,
NULL AS Parent,
'http://test.com/order' AS [xml!1!xmlns:od],
NULL AS [od:Order!2],
NULL AS [od:Order!2!OrderID],
NULL AS [od:Item!3],
NULL AS [od:Item!3!ItemID]
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
'http://test.com/order' AS [xml!1!xmlns:od],
NULL AS [od:Order!2],
OrderID AS [od:Order!2!OrderID],
NULL AS [od:Item!3],
NULL [od:Item!3!ItemID]
FROM @Order
UNION ALL
SELECT
3 AS Tag,
2 AS Parent,
'http://test.com/order' AS [xml!1!xmlns:od],
NULL AS [od:Order!2],
o.OrderID AS [od:Order!2!OrderID],
d.ItemName AS [od:Item!3],
d.ItemID AS [od:Item!3!ItemID]
FROM @Order o INNER JOIN @OrderDetail d ON o.OrderID = d.OrderID
ORDER BY [od:Order!2!OrderID], [od:Item!3!ItemID]
FOR XML EXPLICIT
And see these results:
<xml xmlns:od="http://test.com/order">
<od:Order OrderID="1">
<od:Item ItemID="A">Drink</od:Item>
<od:Item ItemID="B">Cup</od:Item>
</od:Order>
<od:Order OrderID="2">
<od:Item ItemID="A">Drink</od:Item>
<od:Item ItemID="C">Straw</od:Item>
<od:Item ItemID="D">Napkin</od:Item>
</od:Order>
</xml>
XML how to remove namespace from all node except root?
Perhaps inelegant, but the most obvious solution is:
declare @Adr xml = (
select Adr AS [Adr]
from ##TEMP
FOR XML PATH (''), type
);
WITH WITH XMLNAMESPACES ('https://www.my.namespace.com' as ns)
SELECT
Q.DocType AS [@typeP],
Q.RefNo AS [@idP],
Q.Id AS [@kode],
D.DocId AS [@idDoc],
N.NotId AS [@idNot],
CONVERT(char(10), N.CreDate, 126) AS [@date],
'Text' AS [TN/@Z],
@T2 AS [TN/T],
@Adr
FROM
[DB].[dbo].[Q] AS Q
LEFT JOIN [DB].[dbo].[D] AS D ON Q.Id=D.Id
LEFT JOIN [DB].[dbo].[N] AS N ON D.DocId=N.DocuId
WHERE
Q.InboxId= Cast(@InboxId as varchar(15))
FOR XML PATH ('Not');
Which yields...
<Not xmlns:ns="https://www.my.namespace.com"
typeP="4532"
idP="90210"
kode="YCV06N1L-FMDA-YPXZ-5H4F-BLA75C6G86KI"
idDoc="49"
idNot="456"
date="2021-07-19">
<TN Z="Text">
<T>some text</T>
</TN>
<Adr>10800234</Adr>
<Adr>24900005</Adr>
<Adr>24900004</Adr>
<Adr>10201026</Adr>
<Adr>66600019</Adr>
<Adr>14042243</Adr>
</Not>
Remove xmlns= name space declaration when using FOR XML EXPLICIT
If the root element name (i.e. foo in the example data in the Question) is the same across all rows, then there is a simple method for accomplishing this:
specify the AttributeName item (3rd item) in the column name. In terms of the example data, that would be
foo.use the
xmltextDirective (4th item) instead ofxml.
The column name would be:
[x!1!foo!xmltext]
And the full query would be:
SELECT 1 AS [Tag], NULL AS [Parent]
, value AS [x!1!foo!xmltext]
FROM @MyTable
FOR XML EXPLICIT;
But if the root element name does vary, then the above method is not an option and it seems that the only way is to convert the XML data into NVARCHAR (a better choice than VARCHAR since XML and NVARCHAR are both UTF-16):
SELECT 1 AS [Tag], NULL AS [Parent]
, CONVERT(NVARCHAR(MAX), value) AS [x!1!!xml]
FROM @MyTable
FOR XML EXPLICIT;
For more info, please see the MSDN page for Use EXPLICIT Mode with FOR XML.
How can I match the xmlns:* attributes with SQL?
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...
Related Topics
How to Design a Database for User Defined Fields
How to Create a Copy of an Oracle Table Without Copying the Data
How to Check Which Locks Are Held on a Table
Why Is SQL Server Throwing This Error: Cannot Insert the Value Null into Column 'Id'
How to Create a Foreign Key in SQL Server
SQL (Oracle): Order by and Limit
How to Find Current Transaction Level
Calculate Business Days in Oracle SQL(No Functions or Procedure)
Sql: Parse the First, Middle and Last Name from a Fullname Field
Using SQL Function Generate_Series() in Redshift
Excel Function to Make SQL-Like Queries on Worksheet Data
Db Design to Use Sub-Type or Not
Including Null Values in an Apache Spark Join
Insert Into... Merge... Select (SQL Server)
How to Insert Multiple Records and Get the Identity Value
How to Run a Stored Procedure Every Day in SQL Server Express Edition
How to See All the "Special" Characters Permissible in a Varchar or Char Field in SQL Server