Incorrect structure of XML generated in SQL

Question

Iv had another problem, i just change my UNION to UNION ALL and it work correctly but i want to add another XML path (at the beginning) - this will be a const for both querys.

(SELECT 1 AS "ns0:kindOfItem",
code AS "ns0:wholeCode",
REPLACE(weight, ',', '.') AS "ns0:weight",
1 AS "ns0:ammountOfNumbers",
(SELECT price AS "ns0:value",
'EUR' as "ns0:currency"
FOR XML PATH ('ns0:sendedItems'), TYPE),
(SELECT 
'EUR' as "ns0:currency"
FOR XML PATH ('ns0:present'), TYPE)
FROM [PL].[dbo].[dk_documents] where id in (1,2,3)

UNION ALL

(SELECT 1 AS "ns0:kindOfItem",
code AS "ns0:wholeCode",
REPLACE(weight, ',', '.') AS "ns0:weight",
1 AS "ns0:ammountOfNumbers",
(SELECT price AS "ns0:value",
'EUR' as "ns0:currency"
FOR XML PATH ('ns0:sendedItems'), TYPE),
(SELECT 
'EUR' as "ns0:currency"
FOR XML PATH ('ns0:present'), TYPE)
FROM [PL2].[dbo].[dk_documents] where id in (1,2,3)
FOR XML PATH('test'))

It work correctly but i want sth like this :

SELECT 1 as test,
(SELECT 1 AS "ns0:kindOfItem",
code AS "ns0:wholeCode",
REPLACE(weight, ',', '.') AS "ns0:weight",
1 AS "ns0:ammountOfNumbers",
(SELECT price AS "ns0:value",
'EUR' as "ns0:currency"
FOR XML PATH ('ns0:sendedItems'), TYPE),
(SELECT 
'EUR' as "ns0:currency"
FOR XML PATH ('ns0:present'), TYPE)
FROM [PL].[dbo].[dk_documents] where id in (1,2,3)

UNION ALL

(SELECT 1 AS "ns0:kindOfItem",
code AS "ns0:wholeCode",
REPLACE(weight, ',', '.') AS "ns0:weight",
1 AS "ns0:ammountOfNumbers",
(SELECT price AS "ns0:value",
'EUR' as "ns0:currency"
 FOR XML PATH ('ns0:sendedItems'), TYPE),
(SELECT 
'EUR' as "ns0:currency"
FOR XML PATH ('ns0:present'), TYPE)
FROM [PL2].[dbo].[dk_documents] where id in (1,2,3)
FOR XML PATH('test'))
FOR XML PATH('anotherPath')

i got this error:

    Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Output should be like

   <test>1</test>
     <>All of tese columns from QUERY with union ALL</>

Just an example :

WITH XMLNAMESPACES(DEFAULT 'Dummy') 

   SELECT 1 as test,
          2 as anotherOne,
   (SELECT * FROM 
   (SELECT id, symbol  from table1
    WHERE id in (1,2,3)
    UNION ALL
   SELECT id, nrdok from table2
   WHERE id in (4,5,6))as yolo
   FOR XML PATH(''),TYPE)
   FOR XML PATH('test')

It gives me an output :

 <test xmlns="Dummy">
   <test>1</test>
   <anotherOne>2</anotherOne>
   <id xmlns="Dummy">1</id>
   <symbol xmlns="Dummy">test10</symbol>
   <id xmlns="Dummy">2</id>
   <symbol xmlns="Dummy">test10</symbol>
   <id xmlns="Dummy">3</id>
   <symbol xmlns="Dummy">test10</symbol>
   <id xmlns="Dummy">4</id>
   <symbol xmlns="Dummy">test11</symbol>
   <id xmlns="Dummy">5</id>
   <symbol xmlns="Dummy">test11</symbol>
   <id xmlns="Dummy">6</id>
   <symbol xmlns="Dummy">test11</symbol>
</test>

And i want :

  <test xmlns="Dummy">
    <test>1</test>
    <anotherOne>2</anotherOne>
      <id>1</id>
      <symbol>test10</symbol>
      <id>2</id>
      <symbol>test10</symbol>
      <id>3</id>
      <symbol>test10</symbol>
      <id>4</id>
      <symbol>test11</symbol>
      <id>5</id>
      <symbol>test11</symbol>
      <id>6</id>
      <symbol>test11</symbol>
  </test>

You are missing TYPE in `FOR XML PATH('test'))`. To get better answers, provide a self contained script with sample data that can be directly tested. — P. Kouvarakis, May 27 '16 at 07:29
Issue and solution in this SO question: http://stackoverflow.com/questions/3242070/how-do-i-remove-redundant-namespace-in-nested-query-when-using-for-xml-path — P. Kouvarakis, May 27 '16 at 07:58
Are you sure this is the structure you want / need ? For me, it's *weird*. Maybe, for the second test, you should use use something like this ` ... < ` — Bogdan Sahlean, May 27 '16 at 08:50

score 1 · Accepted Answer · edited May 23 '17 at 11:52

As pointed out at your previous question the repeated namespaces are not wrong, just annoying, and - if there are many and long URIs, they can blow up your XML to remarkable size...

There I placed a link to a related question already. The trick is to create the XML without the namespace and add the namespace in the finaly SELECT ... FOR XML PATH only:

But I must admit, that after a long while of trial and error I found, that there seem to be a bug if the DEFAULT namespace is involved. Any approach I tried led to either repeated namespace declarations or to repeated empty namespace declarations.

So the only solution I could find is this (I go to wash my fingers now :-) ):

DECLARE @table1 TABLE(id INT,symbol VARCHAR(100));
INSERT INTO @table1 VALUES
 (1,'Test 1')
,(2,'Test 2')
,(3,'Test 3')
,(4,'Test 4');

DECLARE @nordic_table2 TABLE(id INT,nrdok VARCHAR(100));
INSERT INTO @nordic_table2 VALUES
 (1,'Test 1')
,(2,'Test 2')
,(3,'Test 3')
,(4,'Test 4');


DECLARE @XmlWithoutNamespace XML=
(
 SELECT 1 as test
       ,2 as anotherOne
       ,(
           SELECT * 
           FROM 
           (
            SELECT id, symbol  from @table1
            WHERE id in (1,2,3)
            UNION ALL
            SELECT id, nrdok from @nordic_table2
            WHERE id in (4,5,6)
           ) AS yolo
           FOR XML PATH(''),TYPE
       )
 FOR XML PATH('')
);

SELECT
CAST(
    '<test xmlns="Dummy">'
    +
    CAST(@XmlWithoutNamespace AS NVARCHAR(MAX))
    +
    '</test>'
AS XML);

UPDATE

I strongly advise you to change your structure to this

SELECT id AS [@id]
      ,symbol AS [*]
FROM 
(
SELECT id, symbol  from @table1
WHERE id in (1,2,3)
UNION ALL
SELECT id, nrdok from @nordic_table2
WHERE id in (4,5,6)
) AS yolo
FOR XML PATH('symbol'),TYPE

The result would be this, which is much better to read and to query...

<test xmlns="Dummy">
  <test>1</test>
  <anotherOne>2</anotherOne>
  <symbol id="1">Test 1</symbol>
  <symbol id="2">Test 2</symbol>
  <symbol id="3">Test 3</symbol>
  <symbol id="4">Test 4</symbol>
</test>

UPDATE 2: More Namespaces...

It is actually really hard - almost impossible - to deal with namespaces properly. There is some highly developed logic within FOR XML PATH and in methods like .modify(). I tried several approaches but did not find a convincing one...

The only way I found is very ugly. The trick is, to create 1-level-XML only (no nested elements from sub-selects!) and store them as strings. But before you cut away the root with the namespace declarations. Doing so you'll get invalid XML fragments.

You concatenate them and CAST the whole lot in the last step back to XML.

--Just a container to collect the XML parts
DECLARE @CollectXML TABLE(ID INT IDENTITY,Content XML,CleanedAsString NVARCHAR(MAX));

--The final ",ROOT('xyz')" will force the namespace's declaration into the root node
WITH XMLNAMESPACES('SomeTestUri' AS abc)
INSERT INTO @CollectXML(Content)
SELECT
(
     SELECT 1 as [abc:test]
           ,2 as anotherOne
     FOR XML PATH(''),ROOT('xyz'),TYPE
);
--No we use ugly string manipulation to cut out the inner part without the namespace declaration
UPDATE @CollectXML SET CleanedAsString=
(
    SELECT Rest
    FROM @CollectXML
    CROSS APPLY (SELECT CAST(Content AS NVARCHAR(MAX))) AS Casted(XmlAsString)
    CROSS APPLY (SELECT REVERSE(SUBSTRING(XmlAsString,CHARINDEX('>',XmlAsString)+1,LEN(XmlAsString)))) AS Cut1(part1Reverse)
    CROSS APPLY (SELECT REVERSE(SUBSTRING(part1Reverse,CHARINDEX('<',part1Reverse)+1,LEN(part1Reverse)))) AS Cut2(Rest)
    WHERE ID=1
)
WHERE ID=1;

--The same with the second part
WITH XMLNAMESPACES('SomeTestUri' AS abc)
INSERT INTO @CollectXML(Content)
SELECT 
(
    SELECT id AS [@abc:id]
          ,symbol AS [*]
    FROM 
    (
    SELECT id, symbol  from @table1
    WHERE id in (1,2,3)
    UNION ALL
    SELECT id, nrdok from @nordic_table2
    WHERE id in (4,5,6)
    ) AS yolo
    FOR XML PATH('abc:symbol'),ROOT('xyz'),TYPE --the not needed root will take the namespace declaration out of the deeper elements
);
--and the ugly string manipulation
UPDATE @CollectXML SET CleanedAsString=
(
    SELECT Rest
    FROM @CollectXML
    CROSS APPLY (SELECT CAST(Content AS NVARCHAR(MAX))) AS Casted(XmlAsString)
    CROSS APPLY (SELECT REVERSE(SUBSTRING(XmlAsString,CHARINDEX('>',XmlAsString)+1,LEN(XmlAsString)))) AS Cut1(part1Reverse)
    CROSS APPLY (SELECT REVERSE(SUBSTRING(part1Reverse,CHARINDEX('<',part1Reverse)+1,LEN(part1Reverse)))) AS Cut2(Rest)
    WHERE ID=2
)
WHERE ID=2;

--The XML is put together and - as the very last step! - casted back to XML
SELECT
CAST(
    '<test xmlns="Dummy" xmlns:abc="SomeTestUri">'
    +
    (SELECT CleanedAsString FROM @CollectXML WHERE ID=1)
    +
    (SELECT CleanedAsString FROM @CollectXML WHERE ID=2)
    +
    '</test>'
AS XML);

The result for this

<test xmlns="Dummy" xmlns:abc="SomeTestUri">
  <abc:test>1</abc:test>
  <anotherOne>2</anotherOne>
  <abc:symbol abc:id="1">Test 1</abc:symbol>
  <abc:symbol abc:id="2">Test 2</abc:symbol>
  <abc:symbol abc:id="3">Test 3</abc:symbol>
  <abc:symbol abc:id="4">Test 4</abc:symbol>
</test>

Yes but then i cant use prefix, for example . And then i cant use it like "ns0:test" because this tells me that i dont have prefix declared. Please check out your e-mail which you gave to contact with you(on your profile) — Mati, May 27 '16 at 20:33

score 0 · Answer 2 · answered May 27 '16 at 07:54

0

You can test below SQL Select statement.

I hope it helps,

declare @xml xml
declare @xml1 xml
set @xml1 = '<test>1</test>'
declare @xml2 xml
set @xml2 = ( select top 4 name from sys.databases FOR XML PATH('database') )
set @xml = (SELECT @xml1, @xml2 FOR XML PATH(''))
select  @xml

Output will be as follows

answered May 27 '16 at 07:54

Eralper

6,461
2
21
27

The question is about repeated namespace declarations and how to avoid them, Your answer doesn't even touch this... – Shnugo May 27 '16 at 09:18

Incorrect structure of XML generated in SQL

2 Answers2

UPDATE

UPDATE 2: More Namespaces...