Search code examples
sql-serverxmlt-sqlxpathsqlxml

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:

DECLARE @AccountType NVARCHAR(3);
DECLARE @kodBanke NVARCHAR(3);

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

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 (
  SELECT 
    [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...

<Accounts>
  <Account>
    <BBAN>5710543102313248</BBAN>
    <IBAN>BA39531231634039248</IBAN>
    <AccountType>T</AccountType>
    <Name>DARKO ( DRAGAN ) TESIC</Name>
    <UID>0000005467234</UID>
    <Status>A</Status>
    <Territory>1</Territory>
    <ID_Bank>571</ID_Bank>
    <OpeningDate>20081205-000000</OpeningDate>
    <ClosingDate>20060928-000000</ClosingDate>
  </Account>

... but now AccountNO is missing.

Solution

  • 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>:

    <root>
  <row>
    <FirstNode>blah</FirstNode>
    <SecondNode>
      <OneDeeper>blub</OneDeeper>
      <OneMore>blib</OneMore>
    </SecondNode>
    <ThirdNode>ballaballa</ThirdNode>
  </row>
</root>

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