0

I need to extract data of the BATCH_ID from the table FND_SOA_BODY_PIECE and Column BODY (datatype: CLOB) which has XML data in it

    <?xml version="1.0" encoding="UTF-8"?>        
      <SOAP-ENV:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
        <SOAP-ENV:Header>
        </SOAP-ENV:Header>
        <SOAP-ENV:Body>
            <tns1:InputParameters xmlns:tns1="http://xmlns.oracle.com/apps/gl/soaprovider/plsql/geas_gl_journal_pkg/geas_get_gl_data/">
                  <tns1:P_BATCH_TBL>
                         <tns1:P_BATCH_TBL_ITEM>
                              <tns1:BATCH_ID>85380</tns1:BATCH_ID>
                         </tns1:P_BATCH_TBL_ITEM>
                         <tns1:P_BATCH_TBL_ITEM>
                              <tns1:BATCH_ID>85345</tns1:BATCH_ID>
                         </tns1:P_BATCH_TBL_ITEM>
                         <tns1:P_BATCH_TBL_ITEM>
                              <tns1:BATCH_ID>85364</tns1:BATCH_ID>
                         </tns1:P_BATCH_TBL_ITEM>
                         <tns1:P_BATCH_TBL_ITEM>
                              <tns1:BATCH_ID>85356</tns1:BATCH_ID>
                         </tns1:P_BATCH_TBL_ITEM>
                         <tns1:P_BATCH_TBL_ITEM>
                              <tns1:BATCH_ID>85374</tns1:BATCH_ID>
                         </tns1:P_BATCH_TBL_ITEM>
                         <tns1:P_BATCH_TBL_ITEM>
                              <tns1:BATCH_ID>85358</tns1:BATCH_ID>
                         </tns1:P_BATCH_TBL_ITEM>
                  </tns1:P_BATCH_TBL>
            </tns1:InputParameters>
        </SOAP-ENV:Body>
    </SOAP-ENV:Envelope>

I tried this below but it just gave me null values

    SELECT xmltype(fsa.body).EXTRACT('/Envelope/Body/InputParameters/P_BATCH_TBL/P_BATCH_TBL_ITEM/BATCH_ID').getStringVal() FROM fnd_soa_body_piece;

Could someone help on this one?

Improve this question

1 Answer 1

Reset to default
0

First off, please post valid XML, as what you show looks like a screen scrape from a browser, and is missing the ending tag and had invalid characters. If your xml has namespace references, then you need to include them in the XPath and as a parameter to the extract() function. Here is an example:

with fnd_soa_body_piece as (
select xmltype(
      '<SOAP-ENV:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
           xmlns:xsd="http://www.w3.org/2001/XMLSchema"
           xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" 
           xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
        <SOAP-ENV:Header>
        <SOAP-ENV:Body>
            <tns1:InputParameters xmlns:tns1="http://xmlns.oracle.com/apps/gl/soaprovider/plsql/geas_gl_journal_pkg/geas_get_gl_data/">
                  <tns1:P_BATCH_TBL>
                        <tns1:P_BATCH_TBL_ITEM>
                              <tns1:BATCH_ID>85380</tns1:BATCH_ID>
                         </tns1:P_BATCH_TBL_ITEM>
                        <tns1:P_BATCH_TBL_ITEM>
                              <tns1:BATCH_ID>85345</tns1:BATCH_ID>
                        </tns1:P_BATCH_TBL_ITEM>
                        <tns1:P_BATCH_TBL_ITEM>
                              <tns1:BATCH_ID>85364</tns1:BATCH_ID>
                        </tns1:P_BATCH_TBL_ITEM>
                        <tns1:P_BATCH_TBL_ITEM>
                              <tns1:BATCH_ID>85356</tns1:BATCH_ID>
                        </tns1:P_BATCH_TBL_ITEM>
                        <tns1:P_BATCH_TBL_ITEM>
                              <tns1:BATCH_ID>85374</tns1:BATCH_ID>
                        </tns1:P_BATCH_TBL_ITEM>
                        <tns1:P_BATCH_TBL_ITEM>
                              <tns1:BATCH_ID>85358</tns1:BATCH_ID>
                        </tns1:P_BATCH_TBL_ITEM>
                  </tns1:P_BATCH_TBL>
            </tns1:InputParameters>
        </SOAP-ENV:Body>
        </SOAP-ENV:Header>
    </SOAP-ENV:Envelope>') body from dual
    )
    select extract(body,'/SOAP-ENV:Envelope/SOAP-ENV:Header/SOAP-ENV:Body/tns1:InputParameters/tns1:P_BATCH_TBL/tns1:P_BATCH_TBL_ITEM/tns1:BATCH_ID',
    '  xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" 
       xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" 
       xmlns:tns1="http://xmlns.oracle.com/apps/gl/soaprovider/plsql/geas_gl_journal_pkg/geas_get_gl_data/"').getStringVal()
  from fnd_soa_body_piece

Update:

To return results as a SQL row set, you need to use the XMLTABLE function, such as:

with fnd_soa_body_piece as (
select xmltype(
      '<?xml version="1.0" encoding="UTF-8"?> 
      <SOAP-ENV:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
           xmlns:xsd="http://www.w3.org/2001/XMLSchema"
           xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" 
           xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
        <SOAP-ENV:Header>
        <SOAP-ENV:Body>
            <tns1:InputParameters xmlns:tns1="http://xmlns.oracle.com/apps/gl/soaprovider/plsql/geas_gl_journal_pkg/geas_get_gl_data/">
                  <tns1:P_BATCH_TBL>
                        <tns1:P_BATCH_TBL_ITEM>
                              <tns1:BATCH_ID>85380</tns1:BATCH_ID>
                         </tns1:P_BATCH_TBL_ITEM>
                        <tns1:P_BATCH_TBL_ITEM>
                              <tns1:BATCH_ID>85345</tns1:BATCH_ID>
                        </tns1:P_BATCH_TBL_ITEM>
                        <tns1:P_BATCH_TBL_ITEM>
                              <tns1:BATCH_ID>85364</tns1:BATCH_ID>
                        </tns1:P_BATCH_TBL_ITEM>
                        <tns1:P_BATCH_TBL_ITEM>
                              <tns1:BATCH_ID>85356</tns1:BATCH_ID>
                        </tns1:P_BATCH_TBL_ITEM>
                        <tns1:P_BATCH_TBL_ITEM>
                              <tns1:BATCH_ID>85374</tns1:BATCH_ID>
                        </tns1:P_BATCH_TBL_ITEM>
                        <tns1:P_BATCH_TBL_ITEM>
                              <tns1:BATCH_ID>85358</tns1:BATCH_ID>
                        </tns1:P_BATCH_TBL_ITEM>
                  </tns1:P_BATCH_TBL>
            </tns1:InputParameters>
        </SOAP-ENV:Body>
        </SOAP-ENV:Header>
    </SOAP-ENV:Envelope>') body from dual
    )
    select t.batch_id
    from fnd_soa_body_piece,
    xmltable( xmlnamespaces(
              'http://schemas.xmlsoap.org/soap/encoding/' as "SOAP-ENC",
              'http://schemas.xmlsoap.org/soap/envelope/' as "SOAP-ENV",
              'http://xmlns.oracle.com/apps/gl/soaprovider/plsql/geas_gl_journal_pkg/geas_get_gl_data/' as "tns1" ),
              '/SOAP-ENV:Envelope/SOAP-ENV:Header/SOAP-ENV:Body/tns1:InputParameters/tns1:P_BATCH_TBL/tns1:P_BATCH_TBL_ITEM/tns1:BATCH_ID'
              passing fnd_soa_body_piece.body columns batch_id number(10) PATH 'text()') t
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Thank you for the post!! 1. It works only if I don't have the 1st line of xml <?xml version = '1.0' encoding = 'UTF-8'?> in the xml data How do i handle this? and the second thing is it returns the value in xml format with batch_id tags. How can I just get the data alone?
This is what the output looks like Example: <tns1:BATCH_ID xmlns:tns1="http://xmlns.oracle.com/apps/gl/soaprovider/plsql/geas_gl_journal_pkg/geas_get_gl_data/">85380</tns1:BATCH_ID><tns1:BATCH_ID xmlns:tns1="http://xmlns.oracle.com/apps/gl/soaprovider/plsql/geas_gl_journal_pkg/geas_get_gl_data/">85345</tns1:BATCH_ID>
see update in answer. I have no problem with the XML processing instruction.
This worked perfect!!!. Appreciate your help with this one. Could you also point me to some basic learning content on XML data querying which would help me?
The Oracle docs are very good. Just google "oracle xml db documenation" As you see, namespaces are a pain!
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.