CODEWITHSUNDEEP
sqlxmloracle-databasenested
Victor de Antoni
Victor de Antoni

Reputation: 1

Create nested xml from multiple tables in Oracle SQL

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.

Upvotes: 0

Views: 790

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

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>

Upvotes: 1

Related Questions