Generate XML from SQL issue

I need to generate XML in the following format :

enter image description here

I haven't gone too far with the xml part of the task as I encountered following situation, you can tell that this obviously was not my intention.

enter image description here

How can I handle this properly considering that BBAN and IBAN need to be inside AccountNoas well as that I want it formatted according to the first picture.

The complete query along with my fair attempt of generating xml looks like this:


SET @AccountType = 'T';
SET @kodBanke = ( SELECT vrednost FROM dbini WHERE IDENT = 'KOD' AND SECTION = 'PP' );

WITH Ent_Posta
    SELECT e.naziv,p.posta,e.sifra
    FROM entitet AS e
         INNER JOIN poste AS p ON e.sifra = p.entitet
    [dbo].[brojracuna](@kodBanke,i.partija) AS 'BBAN',
    [dbo].[GENERATEIBAN](i.partija) AS 'IBAN'
     FOR XML PATH('AccountNo'), ELEMENTS, ROOT('Account')
    @accountType 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',
   REPLACE(CONVERT(VARCHAR(10), i.DOTVaRANJE, 120), '.', '') + '-' + RIGHT( '0' + CONVERT(VARCHAR(2), DATEPART(hh, i.DOTVaRANJE)), 2) + '' + RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(mi, i.DOTVaRANJE)), 2) + '' + RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(s, i.DOTVaRANJE)),2) AS OpeningDate,

   REPLACE(CONVERT(VARCHAR(10),'2006.09.28 ', 120), '.', '') + '-' + RIGHT( '0' + CONVERT(VARCHAR(2), DATEPART(hh, '2006.09.28' )), 2) + '' + RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(mi, '2006.09.28 ')), 2) + '' + RIGHT('0' + CONVERT(VARCHAR(2),DATEPART(s,'2006.09.28 ')),2) AS ClosingDate
     FROM adresar AS a
  INNER JOIN istdden AS i 
      ON a.embg = i.embg
        INNER JOIN Ent_Posta as c
        ON a.postbroj = c.posta
           FOR XML PATH ('Account'), ROOT('Accounts');

Upon removal of this part I get...

    <Name>DARKO ( DRAGAN ) TESIC</Name>

... but now AccountNO is missing.


  • If I get this correctly - you are fighting with 1:1 values, but within some deeper nesting.

    You can use an XPath-like expression within [] in connection with FOR XML PATH. Check this out:

    SELECT 'blah' AS FirstNode
          ,'blub' AS [SecondNode/OneDeeper]
          ,'blib' AS [SecondNode/OneMore]
          ,'ballaballa' AS [ThirdNode]
    FOR XML PATH('row'),ROOT('root');

    the resutl (look especially at <SecondNode>:


    For your issue it might be enough to use AS [AccountNo/IBAN] in your query.