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';

Community · Accepted Answer · 2017-05-23 12:15:07Z

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
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.
Updated to fix bug where it would pull values from other branches of the attributes tree.

Collectives™ on Stack Overflow

Extracting multi Row XML Data from POStGreSQL

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related