Create nested xml from multiple tables in Oracle SQL

Question

For a client I need to generate an XML for each account with nested service accounts. For example, I have 3 tables; tAccount, tServiceAccount and tVendor. Table tAccount contains accounts which have one or more service accounts in the tServiceAccount table. Each service account has one vendor in the tVendor table. Ultimately, what I need to do is generate one XML per account (tAccount) and write them to text file but I still have a problem with getting the xml to look the way it should.

My tables are setup as follows:

DROP TABLE tAccount;
DROP TABLE tServiceAccount;
DROP TABLE tVendor;
COMMIT;

CREATE TABLE tAccount
( 
  ACCT_ID number(10) NOT NULL,
  ACCT_NO varchar2(50) NOT NULL,
  ACCT_NM varchar2(50) NOT NULL
);
CREATE UNIQUE INDEX acct_id_pk ON tAccount (ACCT_ID) ;

CREATE TABLE tServiceAccount
( 
  SERVICE_ACCT_ID number(10) NOT NULL,
  ACCT_ID number(10) NOT NULL,
  SERVICE_ACCT_NO varchar2(50) NOT NULL,
  SERVICE_ACCT_NM varchar2(50) NOT NULL,
  VENDOR_ID number(10) NOT NULL
);
CREATE UNIQUE INDEX serviceacct_id_pk ON tServiceAccount (SERVICE_ACCT_ID) ;

CREATE TABLE tVendor
( 
  VENDOR_ID number(10) NOT NULL,
  VENDOR_LOC varchar2(10) NOT NULL,
  VENDOR_NM varchar2(50) NOT NULL
);
CREATE UNIQUE INDEX vendor_id_pk ON tVendor (VENDOR_ID) ;

For this example I use the following data:

INSERT INTO tAccount VALUES (100,'ACT100','Account ABC');
INSERT INTO tAccount VALUES (200,'ACT200','Account XYZ');

INSERT INTO tVendor VALUES (5,'NY','Vendor NY');
INSERT INTO tVendor VALUES (10,'FL','Vendor FL');

INSERT INTO tServiceAccount VALUES (500,100,'E8792347998', 'Location A', 5);
INSERT INTO tServiceAccount VALUES (510,100,'FU809800033', 'Location B', 5);
INSERT INTO tServiceAccount VALUES (520,100,'GR908098988', 'Location C', 10);
INSERT INTO tServiceAccount VALUES (530,100,'PO35948FJ66', 'Location D', 10);
INSERT INTO tServiceAccount VALUES (540,200,'984JFKS5980', 'Location E', 5);
INSERT INTO tServiceAccount VALUES (550,200,'HRW98093993', 'Location F', 10);

The goal is to generate the following xml's:

<root>
    <SourceId>100</SourceId>
    <AccountNumber>ACCT100</AccountNumber>
    <Name>Account ABC</Name>
    <UtilityAccounts>
        <UtilityAccount>
            <SourceId>500</SourceId>
            <UtilityAccountNumber>E8792347998</UtilityAccountNumber>
            <Name>Location A</Name>
            <UtilityName>Vendor NY</UtilityName>
            <UtilityState>NY</UtilityState>
        </UtilityAccount>
        <UtilityAccount>
            <SourceId>510</SourceId>
            <UtilityAccountNumber>FU809800033</UtilityAccountNumber>
            <Name>Location B</Name>
            <UtilityName>Vendor NY</UtilityName>
            <UtilityState>NY</UtilityState>
        </UtilityAccount>
        <UtilityAccount>
            <SourceId>520</SourceId>
            <UtilityAccountNumber>GR908098988</UtilityAccountNumber>
            <Name>Location C</Name>
            <UtilityName>Vendor FL</UtilityName>
            <UtilityState>FL</UtilityState>
        </UtilityAccount>
        <UtilityAccount>
            <SourceId>530</SourceId>
            <UtilityAccountNumber>PO35948FJ66</UtilityAccountNumber>
            <Name>Location D</Name>
            <UtilityName>Vendor FL</UtilityName>
            <UtilityState>FL</UtilityState>
        </UtilityAccount>
    </UtilityAccounts>
</root>

<root>
    <SourceId>200</SourceId>
    <AccountNumber>ACCT200</AccountNumber>
    <Name>Account XYZ</Name>
    <UtilityAccounts>
        <UtilityAccount>
            <SourceId>540</SourceId>
            <UtilityAccountNumber>984JFKS5980</UtilityAccountNumber>
            <Name>Location E</Name>
            <UtilityName>Vendor NY</UtilityName>
            <UtilityState>NY</UtilityState>
        </UtilityAccount>
        <UtilityAccount>
            <SourceId>550</SourceId>
            <UtilityAccountNumber>HRW98093993</UtilityAccountNumber>
            <Name>Location F</Name>
            <UtilityName>Vendor FL</UtilityName>
            <UtilityState>FL</UtilityState>
        </UtilityAccount>
    </UtilityAccounts>
</root>

I have the following query which gives me almost what I need.

SELECT (                                                    
        XMLELEMENT(NAME "root",                                                                             
              XMLFOREST(a.ACCT_ID as "SourceId",                        
                        a.ACCT_NO as "AccountNumber",
                        a.ACCT_NM as "Name",
                    ( SELECT                                         
                        (XMLAGG(                          
                                 XMLFOREST( b.SERVICE_ACCT_NO as "SourceId",      
                                            b.SERVICE_ACCT_NO   as "UtilityAccountNumber",
                                            b.service_acct_nm as "Name",
                                            v.vendor_nm as "UtilityName",
                                            v.vendor_loc as "UtilityState"
                                          )      
                               ))
                               FROM tServiceAccount b join tVendor v on b.vendor_id = v.vendor_id
                                WHERE b.ACCT_ID = a.ACCT_ID            
                    ) AS "UtilityAccounts"
              )
            )
        ) as XMLDOC, a.acct_no as "AcctNo"
FROM tAccount a;

Resulting xml's:

<root>
    <SourceId>100</SourceId>
    <AccountNumber>ACT100</AccountNumber>
    <Name>Account ABC</Name>
    <UtilityAccounts>
        <SourceId>E8792347998</SourceId>
        <UtilityAccountNumber>E8792347998</UtilityAccountNumber>
        <Name>Location A</Name>
        <UtilityName>Vendor NY</UtilityName>
        <UtilityState>NY</UtilityState>
        <SourceId>FU809800033</SourceId>
        <UtilityAccountNumber>FU809800033</UtilityAccountNumber>
        <Name>Location B</Name>
        <UtilityName>Vendor NY</UtilityName>
        <UtilityState>NY</UtilityState>
        <SourceId>GR908098988</SourceId>
        <UtilityAccountNumber>GR908098988</UtilityAccountNumber>
        <Name>Location C</Name>
        <UtilityName>Vendor FL</UtilityName>
        <UtilityState>FL</UtilityState>
        <SourceId>PO35948FJ66</SourceId>
        <UtilityAccountNumber>PO35948FJ66</UtilityAccountNumber>
        <Name>Location D</Name>
        <UtilityName>Vendor FL</UtilityName>
        <UtilityState>FL</UtilityState>
    </UtilityAccounts>
</root>

<root>
    <SourceId>200</SourceId>
    <AccountNumber>ACT200</AccountNumber>
    <Name>Account XYZ</Name>
    <UtilityAccounts>
        <SourceId>984JFKS5980</SourceId>
        <UtilityAccountNumber>984JFKS5980</UtilityAccountNumber>
        <Name>Location E</Name>
        <UtilityName>Vendor NY</UtilityName>
        <UtilityState>NY</UtilityState>
        <SourceId>HRW98093993</SourceId>
        <UtilityAccountNumber>HRW98093993</UtilityAccountNumber>
        <Name>Location F</Name>
        <UtilityName>Vendor FL</UtilityName>
        <UtilityState>FL</UtilityState>
    </UtilityAccounts>
</root>

As you can see, the Utility Accounts are not encapsulated by a <UtilityAccount> element.

Does anyone know how to put each utility account in its own <UtilityAccount> element, like the following?

<UtilityAccounts>
    <UtilityAccount>
        <SourceId>E8792347998</SourceId>
        <UtilityAccountNumber>E8792347998</UtilityAccountNumber>
        <Name>Location A</Name>
        <UtilityName>Vendor NY</UtilityName>
        <UtilityState>NY</UtilityState>
    </UtilityAccount>
    <UtilityAccount>
        <SourceId>FU809800033</SourceId>
        <UtilityAccountNumber>FU809800033</UtilityAccountNumber>
        <Name>Location B</Name>
        <UtilityName>Vendor NY</UtilityName>
        <UtilityState>NY</UtilityState>
    </UtilityAccount>
    <UtilityAccount>
        <SourceId>GR908098988</SourceId>
        <UtilityAccountNumber>GR908098988</UtilityAccountNumber>
        <Name>Location C</Name>
        <UtilityName>Vendor FL</UtilityName>
        <UtilityState>FL</UtilityState>
    </UtilityAccount>   
    <UtilityAccount>
        <SourceId>PO35948FJ66</SourceId>
        <UtilityAccountNumber>PO35948FJ66</UtilityAccountNumber>
        <Name>Location D</Name>
        <UtilityName>Vendor FL</UtilityName>
        <UtilityState>FL</UtilityState>
    </UtilityAccount>
</UtilityAccounts>

Any help will be much appreciated!

Thanks.

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. — Meyssam Toluie
– Meyssam Toluie, Commented Mar 9, 2022 at 20:03

Alex Poole · Accepted Answer · 2022-03-09 17:55:45Z

You just need to create an element to hold the forest, before aggregating, in your subquery:

  SELECT
    XMLAGG(
      XMLELEMENT(
        NAME "UtilityAccount",
        XMLFOREST( b.SERVICE_ACCT_NO as "SourceId",      
                   b.SERVICE_ACCT_NO   as "UtilityAccountNumber",
                   b.service_acct_nm as "Name",
                   v.vendor_nm as "UtilityName",
                   v.vendor_loc as "UtilityState"
        )
      )
    )

or in full (with adjusted formatting/indenting to reduce width):

SELECT
  XMLELEMENT(
    NAME "root",
    XMLFOREST(a.ACCT_ID as "SourceId",
              a.ACCT_NO as "AccountNumber",
              a.ACCT_NM as "Name",
              (
                SELECT
                  XMLAGG(
                    XMLELEMENT(
                      NAME "UtilityAccount",
                      XMLFOREST( b.SERVICE_ACCT_NO as "SourceId",      
                                 b.SERVICE_ACCT_NO   as "UtilityAccountNumber",
                                 b.service_acct_nm as "Name",
                                 v.vendor_nm as "UtilityName",
                                 v.vendor_loc as "UtilityState"
                      )
                    )
                  )
                FROM tServiceAccount b
                JOIN tVendor v ON b.vendor_id = v.vendor_id
                WHERE b.ACCT_ID = a.ACCT_ID            
              ) AS "UtilityAccounts"
     )
  ) as XMLDOC,
  a.acct_no as "AcctNo"
FROM tAccount a;

db<>fiddle, with a plain version and one with XMLSerialize to produce more readable output.

<root>
  <SourceId>100</SourceId>
  <AccountNumber>ACT100</AccountNumber>
  <Name>Account ABC</Name>
  <UtilityAccounts>
    <UtilityAccount>
      <SourceId>E8792347998</SourceId>
      <UtilityAccountNumber>E8792347998</UtilityAccountNumber>
      <Name>Location A</Name>
      <UtilityName>Vendor NY</UtilityName>
      <UtilityState>NY</UtilityState>
    </UtilityAccount>
    <UtilityAccount>
      <SourceId>FU809800033</SourceId>
      <UtilityAccountNumber>FU809800033</UtilityAccountNumber>
      <Name>Location B</Name>
      <UtilityName>Vendor NY</UtilityName>
      <UtilityState>NY</UtilityState>
    </UtilityAccount>
    <UtilityAccount>
      <SourceId>GR908098988</SourceId>
      <UtilityAccountNumber>GR908098988</UtilityAccountNumber>
      <Name>Location C</Name>
      <UtilityName>Vendor FL</UtilityName>
      <UtilityState>FL</UtilityState>
    </UtilityAccount>
    <UtilityAccount>
      <SourceId>PO35948FJ66</SourceId>
      <UtilityAccountNumber>PO35948FJ66</UtilityAccountNumber>
      <Name>Location D</Name>
      <UtilityName>Vendor FL</UtilityName>
      <UtilityState>FL</UtilityState>
    </UtilityAccount>
  </UtilityAccounts>
</root>
<root>
  <SourceId>200</SourceId>
  <AccountNumber>ACT200</AccountNumber>
  <Name>Account XYZ</Name>
  <UtilityAccounts>
    <UtilityAccount>
      <SourceId>984JFKS5980</SourceId>
      <UtilityAccountNumber>984JFKS5980</UtilityAccountNumber>
      <Name>Location E</Name>
      <UtilityName>Vendor NY</UtilityName>
      <UtilityState>NY</UtilityState>
    </UtilityAccount>
    <UtilityAccount>
      <SourceId>HRW98093993</SourceId>
      <UtilityAccountNumber>HRW98093993</UtilityAccountNumber>
      <Name>Location F</Name>
      <UtilityName>Vendor FL</UtilityName>
      <UtilityState>FL</UtilityState>
    </UtilityAccount>
  </UtilityAccounts>
</root>

Thank you Alex Poole for answering my question. The results look great except for one thing that I notice when for instance the Account does not have any ServiceAccounts, it won't display the UtilityAccounts element as empty. The result is then <root> <SourceId>300</SourceId> <AccountNumber>ACT300</AccountNumber> <Name>Account DEF</Name> </root> instead of: <root> <SourceId>300</SourceId> <AccountNumber>ACT300</AccountNumber> <Name>Account DEF</Name> <UtilityAccounts /> </root> How would I then insert <UtilityAccounts /> when the select returns no results? Thanks.

Collectives™ on Stack Overflow

Create nested xml from multiple tables in Oracle SQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related