I need to extract data from a SOAP message

Using PostgreSQL 9.6.

An application stores a SOAP message in a column. I need the value within an XML tag in the SOAP body part to be stored in a new column.

I tried LTRIM, XPATH, SUBSTRING and SPLIT_PART, but it is not working. I suspect XPATH not working because it is not recognizing the SOAP message as XML.

Below is an example of the SOAP message, for readability I entered line breaks manually. In the Database it is stored without line breaks. I separated the part that I need.

    <?xml version="1.0" encoding="UTF-8"?>      <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">      <SOAP-ENV:Body>      <ns0:PRLGOnDemandCreatePdfBasicV1Request xmlns:ns0="http://www.aiasoftware.com/cloud/v1">      <ns0:partner/>      <ns0:customer>G0013860xEC3Dx86C4xEC7Dx4857xB80Dx5E99x0D98xACDD</ns0:customer>      <ns0:contracttypename/>      <ns0:contracttypeversion/>      <ns0:jobid>685769936602576682270215144939</ns0:jobid>      <ns0:documenttemplate>Template1</ns0:documenttemplate>      <ns0:isconcept/>        <ns0:databackbonexml>Part I need</ns0:databackbonexml>        <ns0:status/>      </ns0:PRLGOnDemandCreatePdfBasicV1Request>      </SOAP-ENV:Body>      </SOAP-ENV:Envelope>  

This is the XPath query I tried:

  xpath_exists('/y:PRLGOnDemandCreatePdfBasicV1Request/y:databackbonexml/text()',      esb_berichten.esb_request::xml,       array[array['y', 'aiasoftware.com/cloud/v1']]),  xpath('/x:databackbonexml/text()',       esb_berichten.esb_request::xml,       array[array['x','aiasoftware.com/cloud/v1']])  

This is my query:

SELECT   xpath_exists('/y:PRLGOnDemandCreatePdfBasicV1Request/y:databackbonexml/text()',      esb_berichten.esb_request::xml,       array[array['y', 'aiasoftware.com/cloud/v1']]),  xpath('/x:databackbonexml/text()',       esb_berichten.esb_request::xml,       array[array['x','aiasoftware.com/cloud/v1']])  FROM     public.esb_berichten  WHERE esb_berichten.esb_service = 'RMD_SmartDocuments_ITP_In_The_Cloud' LIMIT 10;  

This is the output:

xpath_exists|xpath  f|{}  f|{}  

I kind of solved it. The problem was partly because the column was cast as text, so I had to cast it to XML.

This is whhat I made:

    SELECT '<?xml version="1.0" encoding="UTF-8"?>      <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">      <SOAP-ENV:Body>      <ns0:PRLGOnDemandCreatePdfBasicV1Request xmlns:ns0="http://www.aiasoftware.com/cloud/v1">      <ns0:partner/>      <ns0:customer>G0013860xEC3Dx86C4xEC7Dx4857xB80Dx5E99x0D98xACDD</ns0:customer>      <ns0:contracttypename/>      <ns0:contracttypeversion/>      <ns0:jobid>685769936602576682270215144939</ns0:jobid>      <ns0:documenttemplate>Template1</ns0:documenttemplate>      <ns0:isconcept/>      <ns0:databackbonexml>SXQncyBsdW5jaCB0aW1l</ns0:databackbonexml>      <ns0:status/>      </ns0:PRLGOnDemandCreatePdfBasicV1Request>      </SOAP-ENV:Body>      </SOAP-ENV:Envelope>      '::xml         )      SELECT      split_part(split_part(p_xml::text, '<ns0:jobid>', 2), '</ns0:jobid>', 1)  AS jobid,      split_part(split_part(p_xml::text, '<ns0:documenttemplate>', 2), '</ns0:documenttemplate>', 1) AS documenttemplate,      split_part(split_part(p_xml::text, '<ns0:isconcept>', 2), '</ns0:isconcept>', 1) AS isconcept,      split_part(split_part(p_xml::text, '<ns0:databackbonexml>', 2), '</ns0:databackbonexml>', 1)::xml AS databackbonexml,      CONVERT_FROM(DECODE(split_part(split_part(p_xml::text, '<ns0:databackbonexml>', 2), '</ns0:databackbonexml>', 1)::text, 'BASE64'), 'UTF-8') AS decoded      FROM   tbl;```  

https://stackoverflow.com/questions/65885486/i-need-to-extract-data-from-a-soap-message January 25, 2021 at 09:14PM