CODEWITHSUNDEEP
sqlxmlxml-namespaces
Firmack Bendera
Firmack Bendera

Reputation: 35

SQL for XML namespace

Please help Me with thing.

I have 2 tables:

#head

PARAM1 PARAM2 PARAM3
------ ------ ------
AAA    BBB    CCC

#body

NAME                 SEX                  EMAIL
-------------------- -------------------- --------------------
Tania                Female               [email protected]
Sergey               male                 [email protected]

To make XML I am using next query:

Query:

DECLARE @XML VARCHAR(1000)
DECLARE @xmlns VARCHAR(1000)

SET @xmlns = 'http://google.example'
SET @XML =

REPLACE(
            (
            SELECT TOP 1
            Param1 as 'Param1',
            Param2 as 'Param2',
            Param3 as 'Param3',
            @XMLNS AS  xmlns,

                (
                    SELECT 
                        NAME as 'NAME',
                        SEX as 'SEX',
                        EMAIL as 'EMAIL'
                    FROM 
                        #body AS BODY 
                    FOR XML PATH('BODY'),TYPE 
                ) 
                FROM 
                    #head AS HEAD
                FOR XML AUTO

            ),' xmlns=""',''
        ) 

SELECT CAST(@XML AS XML)  AS myXML

Current Result IS:

<HEAD xmlns="http://google.example" Param1="AAA" Param2="BBB" Param3="CCC">
  <BODY>
    <NAME>Tania</NAME>
    <SEX>Female</SEX>
    <EMAIL>[email protected]</EMAIL>
  </BODY>
  <BODY>
    <NAME>Sergey</NAME>
    <SEX>male</SEX>
    <EMAIL>[email protected]</EMAIL>
  </BODY>
</HEAD>

Expected Result Is:

<ns0:HEAD Param1="AAA" Param2="BBB" Param3="CCC" xmlns:ns0="http://google.example">
  <BODY>
    <NAME>Tania</NAME>
    <SEX>Female</SEX>
    <EMAIL>[email protected]</EMAIL>
  </BODY>
  <BODY>
    <NAME>Sergey</NAME>
    <SEX>male</SEX>
    <EMAIL>[email protected]</EMAIL>
  </BODY>
</ns0:HEAD>

Does anybody know how to reach expected Result ?

Thanks in advance for any kind of help !

Upvotes: 1

Views: 232

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

If this was SQL-Server (your code looks like this), you might try this:

create table #head(PARAM1 varchar(100), PARAM2 varchar(100), PARAM3 varchar(100));
insert into #head values
('AAA','BBB','CCC');

create table #body(NAME varchar(100), SEX  varchar(100), EMAIL varchar(100));
insert into #body values
 ('Tania','Female','[email protected]')
,('Sergey','male','[email protected]');
GO

--This will add namespaces to the BODY as well. Should not make problems, that's well known issue...

WITH XMLNAMESPACES('http://google.example' AS ns0)
SELECT PARAM1 AS [@Param1]
      ,PARAM2 AS [@Param2]
      ,PARAM3 AS [@Param3]
      ,(
        SELECT NAME,SEX,EMAIL
        FROM #body
        FOR XML PATH('BODY'),TYPE
       )
FROM #head
FOR XML PATH('ns0:HEAD');

--If you want to avoid inner namespaces, you could try this;

DECLARE @bodyXML XML=
(SELECT NAME,SEX,EMAIL
        FROM #body
        FOR XML PATH('BODY'),TYPE
);

WITH XMLNAMESPACES('http://google.example' AS ns0)
SELECT PARAM1 AS [@Param1]
      ,PARAM2 AS [@Param2]
      ,PARAM3 AS [@Param3]
      ,@bodyXML
FROM #head
FOR XML PATH('ns0:HEAD');

--Clean-Up

GO
drop table #head;
drop table #body;

Upvotes: 2

Related Questions