Extracting multi Row XML Data from POStGreSQL

Question

Below I have XML I have to import into POSTGresQL database.

Nothing seems to work.

Each Customer has multiple attributes.

Need to import into a table like this:

create table CustomerAttXML (
    CustomerID varchar(30) NULL,
    AttributeUID varchar(30) NULL,
    AttributeName varchar(50) NULL,
    AttributeValue varchar(50) NULL,
    AttributeUIDValue varchar(50) NULL);

I have almost given up.

Has anyone got any Ideas?

<?xml version="1.0"?>
<Customers>
  <Customer>
    <customerId>C00100000</customerId>
    <title/>
    <firstName>Mary</firstName>
    <lastName>Kennedy</lastName>
    <dob/>
    <mobilePhone>Customer Declined</mobilePhone>
    <primaryEmail>Customer Declined</primaryEmail>
    <primaryAddress1>Customer Declined</primaryAddress1>
    <primaryAddress2>Customer Declined</primaryAddress2>
    <primaryCity>Customer Declined</primaryCity>
    <stateName>Customer Declined</stateName>
    <countryName>Customer Declined</countryName>
    <countryCode>36</countryCode>
    <primaryPostCode>3227</primaryPostCode>
    <homePhone>52222640</homePhone>
    <workPhone/>
    <subscribeToEmail>false</subscribeToEmail>
    <subscribeToSMS>true</subscribeToSMS>
    <Attributes>
      <Attribute>
        <attributeUid>13</attributeUid>
        <attributeValue>false</attributeValue>
        <attribDesc>Inactive Flag</attribDesc>
        <attributeUidValue/>
      </Attribute>
      <Attribute>
        <attributeUid>9</attributeUid>
        <attributeValue>false</attributeValue>
        <attribDesc>Flea &amp; Worming purchase</attribDesc>
        <attributeUidValue/>
      </Attribute>
      <Attribute>
        <attributeUid>7</attributeUid>
        <attributeValue>9308</attributeValue>
        <attribDesc>Store Code</attribDesc>
        <attributeUidValue>0001 Transylvania</attributeUidValue>
      </Attribute>
      <Attribute>
        <attributeUid>16</attributeUid>
        <attributeValue>SODOFF</attributeValue>
        <attribDesc>PasswordClearText</attribDesc>
        <attributeUidValue/>
      </Attribute>
    </Attributes>
  </Customer>
  <Customer>
    <customerId>C00121000</customerId>
    <title/>
    <firstName>Cherie</firstName>
    <lastName>Selby</lastName>
    <dob/>
    <mobilePhone>Customer Declined</mobilePhone>
    <primaryEmail>jCustomer Declinedm</primaryEmail>
    <primaryAddress1>Customer Declined</primaryAddress1>
    <primaryAddress2></primaryAddress2>
    <primaryCity>Customer Declinedl</primaryCity>
    <stateName>Customer Declined</stateName>
    <countryName>Customer Declined</countryName>
    <countryCode>36</countryCode>
    <primaryPostCode>Customer Declined</primaryPostCode>
    <homePhone>Customer Declined</homePhone>
    <workPhone/>
    <subscribeToEmail>true</subscribeToEmail>
    <subscribeToSMS>true</subscribeToSMS>
    <Attributes>
      <Attribute>
        <attributeUid>9</attributeUid>
        <attributeValue>false</attributeValue>
        <attribDesc>Flea &amp; Worming purchase</attribDesc>
        <attributeUidValue/>
      </Attribute>
      <Attribute>
        <attributeUid>13</attributeUid>
        <attributeValue>false</attributeValue>
        <attribDesc>Inactive Flag</attribDesc>
        <attributeUidValue/>
      </Attribute>
      <Attribute>
        <attributeUid>16</attributeUid>
        <attributeValue>Customer Declined</attributeValue>
        <attribDesc>PasswordClearText</attribDesc>
        <attributeUidValue/>
      </Attribute>
      <Attribute>
        <attributeUid>7</attributeUid>
        <attributeValue>Customer Declined</attributeValue>
        <attribDesc>Store Code</attribDesc>
        <attributeUidValue>Customer Declined</attributeUidValue>
      </Attribute>
    </Attributes>
  </Customer>
</Customers>

This is my current solution which does not work:

CREATE OR REPLACE FUNCTION public.import_custxmlattributes ( filename text )
RETURNS VOID AS
'
declare
    myxml xml;
    datafile text := $1;
    EachCustRecord RECORD;
    EachAttrRecord RECORD;

begin

drop table if exists byteb_v;

create temp table byteb_v AS
select bytea_import(datafile);

myxml := (select cast(encode(bytea_import,''escape'') as xml) from byteb_v) ;

drop table if exists CustomerAttXML;

create table CustomerAttXML (
    CustomerID varchar(30) NULL,
    AttributeUID varchar(30) NULL,
    AttributeName varchar(50) NULL,
    AttributeValue varchar(50) NULL,
    AttributeUIDValue varchar(50) NULL);

for EachCustRecord in
 select extract_value(''/Customer/customerId'', x) AS CustomerID,x as individual
 FROM unnest(xpath(''/Customers/Customer'', (select cast(encode(bytea_import,''escape'') as xml) from byteb_v))) x LOOP
  FOR EachAttrRecord IN
  SELECT xpath(''//Attributes/Attribute/attributeUid/text()'', EachCustRecord.individual ) AS AttributeUID,
         xpath(''//Attributes/Attribute/attribDesc/text()'', EachCustRecord.individual) AS AttributeName,
         xpath(''//Attributes/Attribute/attributeValue/text()'', EachCustRecord.individual) AS AttributeValue,
         xpath(''//Attributes/Attribute/attributeUidValue/text()'', EachCustRecord.individual) AS AttributeUIDValue
  FROM unnest(xpath(''/Customers/Customer'', (select cast(encode(bytea_import,''escape'') as xml) from byteb_v))) x LOOP
  RAISE NOTICE ''EachCustRecord.CustomerID(%)'', EachCustRecord.CustomerID;    
  RAISE NOTICE ''EachAttrRecord.AttributeUID(%)'', EachAttrRecord.AttributeUID;     
  RAISE NOTICE ''EachAttrRecord.AttributeName(%)'', EachAttrRecord.AttributeName;     
  RAISE NOTICE ''EachAttrRecord.AttributeValue(%)'', EachAttrRecord.AttributeValue;   
  RAISE NOTICE ''EachAttrRecord.AttributeUIDValue(%)'', EachAttrRecord.AttributeUIDValue;     
/*    insert into CustomerAttXML
    (CustomerID,AttributeUID,AttributeName,AttributeValue,AttributeUIDValue)
    values
    ( EachCustRecord.CustomerID, EachAttrRecord.AttributeUID, EachAttrRecord.AttributeName, EachAttrRecord.AttributeValue, EachAttrRecord.AttributeUIDValue ); */
  END LOOP;
END LOOP;
select pg_sleep(10);
end;'

LANGUAGE 'plpgsql';

score 0 · Accepted Answer · edited May 23 '17 at 12:15

Drawing inspiration from Import XML files to PostgreSQL but done in a nested loop to avoid pulling values from the wrong branches...

CREATE or REPLACE function customerExtractFromXml() 
RETURNS TABLE (custid text, atuid text, atdesc text, atval text, atuval text) as $$
DECLARE
    thexml xml := ('<?xml version="1.0"?>
        <Customers>
          <Customer>
            <customerId>C00100000</customerId>
            <title/>
            <firstName>Mary</firstName>
            <lastName>Kennedy</lastName>
            <dob/>
            <mobilePhone>Customer Declined</mobilePhone>
            <primaryEmail>Customer Declined</primaryEmail>
            <primaryAddress1>Customer Declined</primaryAddress1>
            <primaryAddress2>Customer Declined</primaryAddress2>
            <primaryCity>Customer Declined</primaryCity>
            <stateName>Customer Declined</stateName>
            <countryName>Customer Declined</countryName>
            <countryCode>36</countryCode>
            <primaryPostCode>3227</primaryPostCode>
            <homePhone>52222640</homePhone>
            <workPhone/>
            <subscribeToEmail>false</subscribeToEmail>
            <subscribeToSMS>true</subscribeToSMS>
            <Attributes>
              <Attribute>
            <attributeUid>13</attributeUid>
            <attributeValue>false</attributeValue>
            <attribDesc>Inactive Flag</attribDesc>
            <attributeUidValue/>
              </Attribute>
              <Attribute>
            <attributeUid>9</attributeUid>
            <attributeValue>false</attributeValue>
            <attribDesc>Flea &amp; Worming purchase</attribDesc>
            <attributeUidValue/>
              </Attribute>
              <Attribute>
            <attributeUid>7</attributeUid>
            <attributeValue>9308</attributeValue>
            <attribDesc>Store Code</attribDesc>
            <attributeUidValue>0001 Transylvania</attributeUidValue>
              </Attribute>
              <Attribute>
            <attributeUid>16</attributeUid>
            <attributeValue>SODOFF</attributeValue>
            <attribDesc>PasswordClearText</attribDesc>
            <attributeUidValue/>
              </Attribute>
            </Attributes>
          </Customer>
          <Customer>
            <customerId>C00121000</customerId>
            <title/>
            <firstName>Cherie</firstName>
            <lastName>Selby</lastName>
            <dob/>
            <mobilePhone>Customer Declined</mobilePhone>
            <primaryEmail>jCustomer Declinedm</primaryEmail>
            <primaryAddress1>Customer Declined</primaryAddress1>
            <primaryAddress2></primaryAddress2>
            <primaryCity>Customer Declinedl</primaryCity>
            <stateName>Customer Declined</stateName>
            <countryName>Customer Declined</countryName>
            <countryCode>36</countryCode>
            <primaryPostCode>Customer Declined</primaryPostCode>
            <homePhone>Customer Declined</homePhone>
            <workPhone/>
            <subscribeToEmail>true</subscribeToEmail>
            <subscribeToSMS>true</subscribeToSMS>
            <Attributes>
              <Attribute>
            <attributeUid>9</attributeUid>
            <attributeValue>false</attributeValue>
            <attribDesc>Flea &amp; Worming purchase</attribDesc>
            <attributeUidValue/>
              </Attribute>
              <Attribute>
            <attributeUid>13</attributeUid>
            <attributeValue>false</attributeValue>
            <attribDesc>Inactive Flag</attribDesc>
            <attributeUidValue/>
              </Attribute>
              <Attribute>
            <attributeUid>16</attributeUid>
            <attributeValue>Customer Declined</attributeValue>
            <attribDesc>PasswordClearText</attribDesc>
            <attributeUidValue/>
              </Attribute>
              <Attribute>
            <attributeUid>7</attributeUid>
            <attributeValue>Customer Declined</attributeValue>
            <attribDesc>Store Code</attribDesc>
            <attributeUidValue>Customer Declined</attributeUidValue>
              </Attribute>
            </Attributes>
          </Customer>
        </Customers>
    ')::xml;
    customer text;
    attribute xml;
BEGIN


foreach customer in array xpath('//customerId/text()', thexml) LOOP
    foreach attribute in array xpath('//customerId[.='''||customer||''']/../Attributes/Attribute', thexml) LOOP
            custid := customer;
            atuid  := (xpath('attributeUid/text()', attribute))[1];
            atdesc := (xpath('attribDesc/text()', attribute))[1];
            atval  := (xpath('attributeValue/text()', attribute))[1];
            atuval := (xpath('attributeUidValue/text()', attribute))[1];
        return next ;
    END LOOP;
END LOOP;

END;
$$ LANGUAGE plpgsql;

select * from customerExtractFromXml();

Running this results in:

  custid   | atuid |           atdesc            |       atval       |      atuval       
-----------+-------+-----------------------------+-------------------+-------------------
 C00100000 | 13    | Inactive Flag               | false             | 
 C00100000 | 9     | Flea &amp; Worming purchase | false             | 
 C00100000 | 7     | Store Code                  | 9308              | 0001 Transylvania
 C00100000 | 16    | PasswordClearText           | SODOFF            | 
 C00121000 | 9     | Flea &amp; Worming purchase | false             | 
 C00121000 | 13    | Inactive Flag               | false             | 
 C00121000 | 16    | PasswordClearText           | Customer Declined | 
 C00121000 | 7     | Store Code                  | Customer Declined | Customer Declined
(8 rows)

Narrowing down the sections of XML that it will process was the key - there might be more efficient ways of doing that, possibly with xpath exclusively (although maybe only with xpath v2), I don't know.

Ash

Thanks Ashley, that was the original code, however notice how the attributeUIDValue is not correct. Somehow need to get the Attribute UIDValue to matchwhat actually came in the xml — user1602327, Oct 03 '16 at 22:45
Ahh. Firstly, if you had working (but buggy) code, best to include that to start with - it changes the question from "I have no idea where to even start" to "I have a bug" and would have saved me an hour or two of looking at different solutions and working up a test. — agittins, Oct 04 '16 at 01:42
Updated to fix bug where it would pull values from other branches of the attributes tree. — agittins, Oct 04 '16 at 03:18

Extracting multi Row XML Data from POStGreSQL

1 Answers1