Generate XML from SQL issue

Question

I need to generate XML in the following format :

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.

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.

We don't have access to your data, so we can't run yur SQL. Could you please post some Consumable sample data (DDL and INSERT statements)? Otherwise, all i can suggest is that the reason you are getting escaped characters in the sub nodes is because you haven't use the TYPE operator. — Thom A
– Thom A ♦, Commented Feb 16, 2018 at 14:07

Nimantha · Accepted Answer · 2023-11-09 09:00:32Z

1

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.

edited Nov 9, 2023 at 9:00

Nimantha

6,5476 gold badges32 silver badges78 bronze badges

answered Feb 16, 2018 at 14:14

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Generate XML from SQL issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related