Search code examples
sql-serverxmlt-sqlsqlxml

Modifying XML output

I have generated the XML in the following manner, except for the tag I bordered in red.

enter image description here

How can I add the following line marked red to the Accounts tag: ba.cbbh.crr.retail so that it looks as indicated in the photo above? (Now it looks like this)

Consider that I have to avoid XML syntax errors, such as blank space but retain blank space after Accounts.

(<Accounts xmlns="ba.cbbh.crr.retailba.cbbh.crr.retail">)

I tried this but nothing changes...

DECLARED ALL...

SET @ACCOUNTTYPE = 'T';

SET @kodBanke = ( SELECT vrednost FROM dbini WHERE IDENT = 'KOD' AND SECTION = 'PP' );
SET @XML_info = '<?xml version="1.0" encoding="UTF-8"?>';
SET @xmlnsDodatak =  'ba.cbbh.crr.retail';



IF (@tabelaTipaRacuna = 'istdev')
BEGIN
SELECT @SQL =  'WITH Ent_Posta
            AS
        (
           SELECT e.naziv,p.posta,e.sifra
            FROM entitet AS e
          INNER JOIN poste AS p ON e.sifra = p.entitet
        )

           SELECT [dbo].[brojracuna](' + @kodBanke + ',i.partija) AS ''AccountNo/BBAN'',
           [dbo].[GENERATEIBAN](i.partija) AS ''AccountNo/IBAN'', 
           ' + '''D''' + ' AS ''AccountType'',
           (a.ime + ''('' + a.roditel + '')''  + a.prezime) AS ''Name'',
           a.embg AS ''UID'',
       CASE status 
       WHEN 2 THEN ''A'' 
       WHEN 4 THEN ''B''
       WHEN 8 THEN ''U''
  END AS ''Status'',
  c.sifra AS ''Territory'',
  ' + @kodBanke + ' as ''ID_Bank'',
  CONVERT(DATETIME,' + 'i.dotvoranje' + ',120) AS ''OpeningDate'',
  ISNULL(CONVERT(DATETIME,' + '1' + ',120),'''') AS ''ClosingDate''
 FROM adresar AS a
  INNER JOIN' + QUOTENAME(@tabelaTipaRacuna) + ' AS i 
      ON a.embg = i.embg
        INNER JOIN Ent_Posta as c
        ON a.postbroj = c.posta 
        FOR XML PATH(''Account''), ROOT(''Accounts'')'

         SELECT @SQL = REPLACE(@SQL, '''<Accounts>''', '''<Accounts xmlns="' + @xmlnsDodatak + '">''')
         SELECT @SQL
         EXEC(@SQL)

Solution:

This is what worked for me...

 ...'WITH XMLNAMESPACES(DEFAULT ''ba.cbbh.crr.retail''), Ent_Posta
            AS
        (
           SELECT e.naziv,p.posta,e.sifra
            FROM entitet AS e
          INNER JOIN poste AS p ON e.sifra = p.entitet
        )...



... INNER JOIN Ent_Posta as c
    ON a.postbroj = c.posta 
    FOR XML PATH(''Account''), ROOT(''Accounts'')'...

Edit:

Now I want this:

enter image description here

After researching a bit I figured out it should look something like this:

...SELECT @SQL = 'SELECT ''<?xml version=""1.0"" encoding=""UTF-8""?>''' + ''

         'WITH XMLNAMESPACES(DEFAULT ''ba.cbbh.crr.retail''), Ent_Posta
            AS
        (
           SELECT e.naziv,p.posta,e.sifra
            FROM entitet AS e
          INNER JOIN poste AS p ON e.sifra = p.entitet
        ) ...

Apparently, it won't work. What is wrong with this?

Solution

  • That's a default namespace. So follow this example:

    Specifying Default Namespaces Instead of declaring a namespace prefix, you can declare a default namespace by using a DEFAULT keyword. In the FOR XML query, it will bind the default namespace to XML nodes in the resulting XML. In the following example, the WITH XMLNAMESPACES defines two namespace prefixes that are defined together with a default namespace.

    WITH XMLNAMESPACES ('uri1' as ns1,   
                    'uri2' as ns2,  
                    DEFAULT 'uri2')  
SELECT ProductID,   
      Name,  
      Color  
FROM Production.Product   
WHERE ProductID=316 or ProductID=317  
FOR XML RAW ('ns1:Product'), ROOT('ns2:root'), ELEMENTS

    Add Namespaces to Queries with WITH XMLNAMESPACES

    You have a CTE, and to combine that with XMLNAMESPACES would look like:

    WITH XMLNAMESPACES ('uri1' as ns1,   
                    'uri2' as ns2,  
                    DEFAULT 'uri2'),
q as
(
SELECT ProductID,   
      Name,  
      Color  
FROM Production.Product   
WHERE ProductID=316 or ProductID=317  
)
select *
from q
FOR XML RAW ('ns1:Product'), ROOT('ns2:root'), ELEMENTS