Oracle, XML to insert statement












1














I have sample xml



<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">

    <soapenv:Body>

        <tns:getAbcResponse xmlns:tns="http://xyz.abc.com">

            <abc-name>CONTRACT_STATUS</abc-name>

            <abc-values>

                <attribute>

                    <name>7</name>

                    <value-list>

                        <value>

                            <name>CONTRACT_STATUS</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>CONTRACT_STATUS_DESC</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>SOURCE_SYSTEM</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>SOURCE_VALUE</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>SOURCE_DESC</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>VISIBLE</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>REQUIRED</name>

                            <value>xyzabc</value>

                        </value>

                    </value-list>

                </attribute>

                <attribute>

                    <name>7</name>

                    <value-list>

                        <value>

                            <name>CONTRACT_STATUS</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>CONTRACT_STATUS_DESC</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>SOURCE_SYSTEM</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>SOURCE_VALUE</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>SOURCE_DESC</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>VISIBLE</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>REQUIRED</name>

                            <value>xyzabc</value>

                        </value>

                    </value-list>

                </attribute>

            </abc-values>

            <status error-code="0">

                <error-msg>OK</error-msg>

            </status>

        </tns:getAbcResponse>

    </soapenv:Body>

</soapenv:Envelope>


In this sample, one <attribute> tag it's 1 row, which i need to insert into table.



I managed to build a query:



  with response(xm) as ( select xmltype('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soapenv:Body><tns:getAbcResponse xmlns:tns="http://xyz.abc.com"><abc-name>CONTRACT_STATUS</abc-name><abc-values>

<attribute><name>7</name><value-list><value><name>CONTRACT_STATUS</name><value>xyzabc</value></value><value><name>CONTRACT_STATUS_DESC</name><value>xyzabc</value></value><value><name>SOURCE_SYSTEM</name><value>xyzabc</value></value><value><name>SOURCE_VALUE</name><value>xyzabc</value></value><value><name>SOURCE_DESC</name><value>xyzabc</value></value><value><name>VISIBLE</name><value>xyzabc</value></value><value><name>REQUIRED</name><value>xyzabc</value></value></value-list></attribute>

<attribute><name>7</name><value-list><value><name>CONTRACT_STATUS</name><value>xyzabc</value></value><value><name>CONTRACT_STATUS_DESC</name><value>xyzabc</value></value><value><name>SOURCE_SYSTEM</name><value>xyzabc</value></value><value><name>SOURCE_VALUE</name><value>xyzabc</value></value><value><name>SOURCE_DESC</name><value>xyzabc</value></value><value><name>VISIBLE</name><value>xyzabc</value></value><value><name>REQUIRED</name><value>xyzabc</value></value></value-list></attribute>

</abc-values><status error-code="0"><error-msg>OK</error-msg></status></tns:getAbcResponse></soapenv:Body></soapenv:Envelope>')from dual)

SELECT  n.att_name  AS name,

        v.name      AS att_name,

        v.value     AS att_value

FROM    response r,

        XMLTABLE

        (

          '//abc-values/attribute'

          PASSING r.xm

          COLUMNS

            att_name VARCHAR2(30) PATH 'name',

            value_list XMLTYPE PATH 'value-list'

        ) n,

        XMLTABLE 

        ( 

          'value-list/value'

          PASSING n.value_list

          COLUMNS

            name   varchar2(30) PATH 'name',

            value  varchar2(30) PATH 'value'

        ) v;


With that approach, I need to:




  1. get first 7 rows from this query,

  2. transform into single insert query -> execute / put into collection

  3. get next 7 rows from this query

  4. etc.


In other way:



insert



NOTE: name column is some kind of id and repeated for every row from table v.



And I have absolutely no idea how to achieve this.
Any ideas, diffrent approaches will be very welcome.



Thanks :)






xml oracle web-services plsql







share|improve this question




















  • 1




    What is it that differentiates the first 7 rows from the second 7 rows? I mean, your sample data is the same for everything (e.g. both sets have the same values in the same places). Is it always 7 rows?
    – Boneist
    Nov 19 '18 at 14:46










  • I simply obfuscated data.
    – q4za4
    Nov 19 '18 at 14:52






  • 1




    It helps to provide sample data that accurately reflects the requirement you're trying to handle, though!
    – Boneist
    Nov 19 '18 at 15:04










  • In this case it really don't. I'am trying operate on column names (like contract_status, source_system), so mechanism supposed to not give a cr... about data. If xml contains 'xyz' - program will insert 'xyz'. If 'abc' then 'abc'. You can add a diffrent number on the end of every 'xyzabc' if it helps You.
    – q4za4
    Nov 19 '18 at 15:11






  • 1




    it really does. If all the data is the same, how do you know you're fetching the right value in the correct place? As it is, I added numbers to the sample data for clarity in my answer, so you can know for sure the values are being returned as expected. That should really have been done by you, since you're the one asking for help. Please ensure you provide useful sample data next time you raise a question, to avoid wasting everyone's time.
    – Boneist
    Nov 19 '18 at 15:14
















1














I have sample xml



<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">

    <soapenv:Body>

        <tns:getAbcResponse xmlns:tns="http://xyz.abc.com">

            <abc-name>CONTRACT_STATUS</abc-name>

            <abc-values>

                <attribute>

                    <name>7</name>

                    <value-list>

                        <value>

                            <name>CONTRACT_STATUS</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>CONTRACT_STATUS_DESC</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>SOURCE_SYSTEM</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>SOURCE_VALUE</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>SOURCE_DESC</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>VISIBLE</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>REQUIRED</name>

                            <value>xyzabc</value>

                        </value>

                    </value-list>

                </attribute>

                <attribute>

                    <name>7</name>

                    <value-list>

                        <value>

                            <name>CONTRACT_STATUS</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>CONTRACT_STATUS_DESC</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>SOURCE_SYSTEM</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>SOURCE_VALUE</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>SOURCE_DESC</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>VISIBLE</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>REQUIRED</name>

                            <value>xyzabc</value>

                        </value>

                    </value-list>

                </attribute>

            </abc-values>

            <status error-code="0">

                <error-msg>OK</error-msg>

            </status>

        </tns:getAbcResponse>

    </soapenv:Body>

</soapenv:Envelope>


In this sample, one <attribute> tag it's 1 row, which i need to insert into table.



I managed to build a query:



  with response(xm) as ( select xmltype('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soapenv:Body><tns:getAbcResponse xmlns:tns="http://xyz.abc.com"><abc-name>CONTRACT_STATUS</abc-name><abc-values>

<attribute><name>7</name><value-list><value><name>CONTRACT_STATUS</name><value>xyzabc</value></value><value><name>CONTRACT_STATUS_DESC</name><value>xyzabc</value></value><value><name>SOURCE_SYSTEM</name><value>xyzabc</value></value><value><name>SOURCE_VALUE</name><value>xyzabc</value></value><value><name>SOURCE_DESC</name><value>xyzabc</value></value><value><name>VISIBLE</name><value>xyzabc</value></value><value><name>REQUIRED</name><value>xyzabc</value></value></value-list></attribute>

<attribute><name>7</name><value-list><value><name>CONTRACT_STATUS</name><value>xyzabc</value></value><value><name>CONTRACT_STATUS_DESC</name><value>xyzabc</value></value><value><name>SOURCE_SYSTEM</name><value>xyzabc</value></value><value><name>SOURCE_VALUE</name><value>xyzabc</value></value><value><name>SOURCE_DESC</name><value>xyzabc</value></value><value><name>VISIBLE</name><value>xyzabc</value></value><value><name>REQUIRED</name><value>xyzabc</value></value></value-list></attribute>

</abc-values><status error-code="0"><error-msg>OK</error-msg></status></tns:getAbcResponse></soapenv:Body></soapenv:Envelope>')from dual)

SELECT  n.att_name  AS name,

        v.name      AS att_name,

        v.value     AS att_value

FROM    response r,

        XMLTABLE

        (

          '//abc-values/attribute'

          PASSING r.xm

          COLUMNS

            att_name VARCHAR2(30) PATH 'name',

            value_list XMLTYPE PATH 'value-list'

        ) n,

        XMLTABLE 

        ( 

          'value-list/value'

          PASSING n.value_list

          COLUMNS

            name   varchar2(30) PATH 'name',

            value  varchar2(30) PATH 'value'

        ) v;


With that approach, I need to:




  1. get first 7 rows from this query,

  2. transform into single insert query -> execute / put into collection

  3. get next 7 rows from this query

  4. etc.


In other way:



insert



NOTE: name column is some kind of id and repeated for every row from table v.



And I have absolutely no idea how to achieve this.
Any ideas, diffrent approaches will be very welcome.



Thanks :)






xml oracle web-services plsql







share|improve this question




















  • 1




    What is it that differentiates the first 7 rows from the second 7 rows? I mean, your sample data is the same for everything (e.g. both sets have the same values in the same places). Is it always 7 rows?
    – Boneist
    Nov 19 '18 at 14:46










  • I simply obfuscated data.
    – q4za4
    Nov 19 '18 at 14:52






  • 1




    It helps to provide sample data that accurately reflects the requirement you're trying to handle, though!
    – Boneist
    Nov 19 '18 at 15:04










  • In this case it really don't. I'am trying operate on column names (like contract_status, source_system), so mechanism supposed to not give a cr... about data. If xml contains 'xyz' - program will insert 'xyz'. If 'abc' then 'abc'. You can add a diffrent number on the end of every 'xyzabc' if it helps You.
    – q4za4
    Nov 19 '18 at 15:11






  • 1




    it really does. If all the data is the same, how do you know you're fetching the right value in the correct place? As it is, I added numbers to the sample data for clarity in my answer, so you can know for sure the values are being returned as expected. That should really have been done by you, since you're the one asking for help. Please ensure you provide useful sample data next time you raise a question, to avoid wasting everyone's time.
    – Boneist
    Nov 19 '18 at 15:14














1












1








1







I have sample xml



<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">

    <soapenv:Body>

        <tns:getAbcResponse xmlns:tns="http://xyz.abc.com">

            <abc-name>CONTRACT_STATUS</abc-name>

            <abc-values>

                <attribute>

                    <name>7</name>

                    <value-list>

                        <value>

                            <name>CONTRACT_STATUS</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>CONTRACT_STATUS_DESC</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>SOURCE_SYSTEM</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>SOURCE_VALUE</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>SOURCE_DESC</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>VISIBLE</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>REQUIRED</name>

                            <value>xyzabc</value>

                        </value>

                    </value-list>

                </attribute>

                <attribute>

                    <name>7</name>

                    <value-list>

                        <value>

                            <name>CONTRACT_STATUS</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>CONTRACT_STATUS_DESC</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>SOURCE_SYSTEM</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>SOURCE_VALUE</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>SOURCE_DESC</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>VISIBLE</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>REQUIRED</name>

                            <value>xyzabc</value>

                        </value>

                    </value-list>

                </attribute>

            </abc-values>

            <status error-code="0">

                <error-msg>OK</error-msg>

            </status>

        </tns:getAbcResponse>

    </soapenv:Body>

</soapenv:Envelope>


In this sample, one <attribute> tag it's 1 row, which i need to insert into table.



I managed to build a query:



  with response(xm) as ( select xmltype('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soapenv:Body><tns:getAbcResponse xmlns:tns="http://xyz.abc.com"><abc-name>CONTRACT_STATUS</abc-name><abc-values>

<attribute><name>7</name><value-list><value><name>CONTRACT_STATUS</name><value>xyzabc</value></value><value><name>CONTRACT_STATUS_DESC</name><value>xyzabc</value></value><value><name>SOURCE_SYSTEM</name><value>xyzabc</value></value><value><name>SOURCE_VALUE</name><value>xyzabc</value></value><value><name>SOURCE_DESC</name><value>xyzabc</value></value><value><name>VISIBLE</name><value>xyzabc</value></value><value><name>REQUIRED</name><value>xyzabc</value></value></value-list></attribute>

<attribute><name>7</name><value-list><value><name>CONTRACT_STATUS</name><value>xyzabc</value></value><value><name>CONTRACT_STATUS_DESC</name><value>xyzabc</value></value><value><name>SOURCE_SYSTEM</name><value>xyzabc</value></value><value><name>SOURCE_VALUE</name><value>xyzabc</value></value><value><name>SOURCE_DESC</name><value>xyzabc</value></value><value><name>VISIBLE</name><value>xyzabc</value></value><value><name>REQUIRED</name><value>xyzabc</value></value></value-list></attribute>

</abc-values><status error-code="0"><error-msg>OK</error-msg></status></tns:getAbcResponse></soapenv:Body></soapenv:Envelope>')from dual)

SELECT  n.att_name  AS name,

        v.name      AS att_name,

        v.value     AS att_value

FROM    response r,

        XMLTABLE

        (

          '//abc-values/attribute'

          PASSING r.xm

          COLUMNS

            att_name VARCHAR2(30) PATH 'name',

            value_list XMLTYPE PATH 'value-list'

        ) n,

        XMLTABLE 

        ( 

          'value-list/value'

          PASSING n.value_list

          COLUMNS

            name   varchar2(30) PATH 'name',

            value  varchar2(30) PATH 'value'

        ) v;


With that approach, I need to:




  1. get first 7 rows from this query,

  2. transform into single insert query -> execute / put into collection

  3. get next 7 rows from this query

  4. etc.


In other way:



insert



NOTE: name column is some kind of id and repeated for every row from table v.



And I have absolutely no idea how to achieve this.
Any ideas, diffrent approaches will be very welcome.



Thanks :)






xml oracle web-services plsql







share|improve this question















I have sample xml



<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">

    <soapenv:Body>

        <tns:getAbcResponse xmlns:tns="http://xyz.abc.com">

            <abc-name>CONTRACT_STATUS</abc-name>

            <abc-values>

                <attribute>

                    <name>7</name>

                    <value-list>

                        <value>

                            <name>CONTRACT_STATUS</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>CONTRACT_STATUS_DESC</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>SOURCE_SYSTEM</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>SOURCE_VALUE</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>SOURCE_DESC</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>VISIBLE</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>REQUIRED</name>

                            <value>xyzabc</value>

                        </value>

                    </value-list>

                </attribute>

                <attribute>

                    <name>7</name>

                    <value-list>

                        <value>

                            <name>CONTRACT_STATUS</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>CONTRACT_STATUS_DESC</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>SOURCE_SYSTEM</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>SOURCE_VALUE</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>SOURCE_DESC</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>VISIBLE</name>

                            <value>xyzabc</value>

                        </value>

                        <value>

                            <name>REQUIRED</name>

                            <value>xyzabc</value>

                        </value>

                    </value-list>

                </attribute>

            </abc-values>

            <status error-code="0">

                <error-msg>OK</error-msg>

            </status>

        </tns:getAbcResponse>

    </soapenv:Body>

</soapenv:Envelope>


In this sample, one <attribute> tag it's 1 row, which i need to insert into table.



I managed to build a query:



  with response(xm) as ( select xmltype('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soapenv:Body><tns:getAbcResponse xmlns:tns="http://xyz.abc.com"><abc-name>CONTRACT_STATUS</abc-name><abc-values>

<attribute><name>7</name><value-list><value><name>CONTRACT_STATUS</name><value>xyzabc</value></value><value><name>CONTRACT_STATUS_DESC</name><value>xyzabc</value></value><value><name>SOURCE_SYSTEM</name><value>xyzabc</value></value><value><name>SOURCE_VALUE</name><value>xyzabc</value></value><value><name>SOURCE_DESC</name><value>xyzabc</value></value><value><name>VISIBLE</name><value>xyzabc</value></value><value><name>REQUIRED</name><value>xyzabc</value></value></value-list></attribute>

<attribute><name>7</name><value-list><value><name>CONTRACT_STATUS</name><value>xyzabc</value></value><value><name>CONTRACT_STATUS_DESC</name><value>xyzabc</value></value><value><name>SOURCE_SYSTEM</name><value>xyzabc</value></value><value><name>SOURCE_VALUE</name><value>xyzabc</value></value><value><name>SOURCE_DESC</name><value>xyzabc</value></value><value><name>VISIBLE</name><value>xyzabc</value></value><value><name>REQUIRED</name><value>xyzabc</value></value></value-list></attribute>

</abc-values><status error-code="0"><error-msg>OK</error-msg></status></tns:getAbcResponse></soapenv:Body></soapenv:Envelope>')from dual)

SELECT  n.att_name  AS name,

        v.name      AS att_name,

        v.value     AS att_value

FROM    response r,

        XMLTABLE

        (

          '//abc-values/attribute'

          PASSING r.xm

          COLUMNS

            att_name VARCHAR2(30) PATH 'name',

            value_list XMLTYPE PATH 'value-list'

        ) n,

        XMLTABLE 

        ( 

          'value-list/value'

          PASSING n.value_list

          COLUMNS

            name   varchar2(30) PATH 'name',

            value  varchar2(30) PATH 'value'

        ) v;


With that approach, I need to:




  1. get first 7 rows from this query,

  2. transform into single insert query -> execute / put into collection

  3. get next 7 rows from this query

  4. etc.


In other way:



insert



NOTE: name column is some kind of id and repeated for every row from table v.



And I have absolutely no idea how to achieve this.
Any ideas, diffrent approaches will be very welcome.



Thanks :)






xml oracle web-services plsql




xml oracle web-services plsql






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 19 '18 at 13:55

























asked Nov 19 '18 at 13:44









q4za4

465




465








  • 1




    What is it that differentiates the first 7 rows from the second 7 rows? I mean, your sample data is the same for everything (e.g. both sets have the same values in the same places). Is it always 7 rows?
    – Boneist
    Nov 19 '18 at 14:46










  • I simply obfuscated data.
    – q4za4
    Nov 19 '18 at 14:52






  • 1




    It helps to provide sample data that accurately reflects the requirement you're trying to handle, though!
    – Boneist
    Nov 19 '18 at 15:04










  • In this case it really don't. I'am trying operate on column names (like contract_status, source_system), so mechanism supposed to not give a cr... about data. If xml contains 'xyz' - program will insert 'xyz'. If 'abc' then 'abc'. You can add a diffrent number on the end of every 'xyzabc' if it helps You.
    – q4za4
    Nov 19 '18 at 15:11






  • 1




    it really does. If all the data is the same, how do you know you're fetching the right value in the correct place? As it is, I added numbers to the sample data for clarity in my answer, so you can know for sure the values are being returned as expected. That should really have been done by you, since you're the one asking for help. Please ensure you provide useful sample data next time you raise a question, to avoid wasting everyone's time.
    – Boneist
    Nov 19 '18 at 15:14














  • 1




    What is it that differentiates the first 7 rows from the second 7 rows? I mean, your sample data is the same for everything (e.g. both sets have the same values in the same places). Is it always 7 rows?
    – Boneist
    Nov 19 '18 at 14:46










  • I simply obfuscated data.
    – q4za4
    Nov 19 '18 at 14:52






  • 1




    It helps to provide sample data that accurately reflects the requirement you're trying to handle, though!
    – Boneist
    Nov 19 '18 at 15:04










  • In this case it really don't. I'am trying operate on column names (like contract_status, source_system), so mechanism supposed to not give a cr... about data. If xml contains 'xyz' - program will insert 'xyz'. If 'abc' then 'abc'. You can add a diffrent number on the end of every 'xyzabc' if it helps You.
    – q4za4
    Nov 19 '18 at 15:11






  • 1




    it really does. If all the data is the same, how do you know you're fetching the right value in the correct place? As it is, I added numbers to the sample data for clarity in my answer, so you can know for sure the values are being returned as expected. That should really have been done by you, since you're the one asking for help. Please ensure you provide useful sample data next time you raise a question, to avoid wasting everyone's time.
    – Boneist
    Nov 19 '18 at 15:14








1




1




What is it that differentiates the first 7 rows from the second 7 rows? I mean, your sample data is the same for everything (e.g. both sets have the same values in the same places). Is it always 7 rows?
– Boneist
Nov 19 '18 at 14:46




What is it that differentiates the first 7 rows from the second 7 rows? I mean, your sample data is the same for everything (e.g. both sets have the same values in the same places). Is it always 7 rows?
– Boneist
Nov 19 '18 at 14:46












I simply obfuscated data.
– q4za4
Nov 19 '18 at 14:52




I simply obfuscated data.
– q4za4
Nov 19 '18 at 14:52




1




1




It helps to provide sample data that accurately reflects the requirement you're trying to handle, though!
– Boneist
Nov 19 '18 at 15:04




It helps to provide sample data that accurately reflects the requirement you're trying to handle, though!
– Boneist
Nov 19 '18 at 15:04












In this case it really don't. I'am trying operate on column names (like contract_status, source_system), so mechanism supposed to not give a cr... about data. If xml contains 'xyz' - program will insert 'xyz'. If 'abc' then 'abc'. You can add a diffrent number on the end of every 'xyzabc' if it helps You.
– q4za4
Nov 19 '18 at 15:11




In this case it really don't. I'am trying operate on column names (like contract_status, source_system), so mechanism supposed to not give a cr... about data. If xml contains 'xyz' - program will insert 'xyz'. If 'abc' then 'abc'. You can add a diffrent number on the end of every 'xyzabc' if it helps You.
– q4za4
Nov 19 '18 at 15:11




1




1




it really does. If all the data is the same, how do you know you're fetching the right value in the correct place? As it is, I added numbers to the sample data for clarity in my answer, so you can know for sure the values are being returned as expected. That should really have been done by you, since you're the one asking for help. Please ensure you provide useful sample data next time you raise a question, to avoid wasting everyone's time.
– Boneist
Nov 19 '18 at 15:14




it really does. If all the data is the same, how do you know you're fetching the right value in the correct place? As it is, I added numbers to the sample data for clarity in my answer, so you can know for sure the values are being returned as expected. That should really have been done by you, since you're the one asking for help. Please ensure you provide useful sample data next time you raise a question, to avoid wasting everyone's time.
– Boneist
Nov 19 '18 at 15:14












2 Answers
2






active

oldest

votes


















2














Here's one way of selecting the data:



with response(xm) as ( select xmltype('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soapenv:Body><tns:getAbcResponse xmlns:tns="http://xyz.abc.com"><abc-name>CONTRACT_STATUS</abc-name><abc-values>

<attribute><name>7</name><value-list><value><name>CONTRACT_STATUS</name><value>11xyzabc</value></value><value><name>CONTRACT_STATUS_DESC</name><value>12xyzabc</value></value><value><name>SOURCE_SYSTEM</name><value>13xyzabc</value></value><value><name>SOURCE_VALUE</name><value>14xyzabc</value></value><value><name>SOURCE_DESC</name><value>15xyzabc</value></value><value><name>VISIBLE</name><value>16xyzabc</value></value><value><name>REQUIRED</name><value>17xyzabc</value></value></value-list></attribute>

<attribute><name>7</name><value-list><value><name>CONTRACT_STATUS</name><value>21xyzabc</value></value><value><name>CONTRACT_STATUS_DESC</name><value>22xyzabc</value></value><value><name>SOURCE_SYSTEM</name><value>23xyzabc</value></value><value><name>SOURCE_VALUE</name><value>24xyzabc</value></value><value><name>SOURCE_DESC</name><value>25xyzabc</value></value><value><name>VISIBLE</name><value>26xyzabc</value></value><value><name>REQUIRED</name><value>27xyzabc</value></value></value-list></attribute>

</abc-values><status error-code="0"><error-msg>OK</error-msg></status></tns:getAbcResponse></soapenv:Body></soapenv:Envelope>') from dual)

SELECT  n.att_name  AS name,

        v.contract_status,

        v.contract_status_desc,

        v.source_system,

        v.source_value,

        v.source_desc,

        v.visible,

        v.required

FROM    response r,

        XMLTABLE

        (

          '//abc-values/attribute'

          PASSING r.xm

          COLUMNS

            att_name VARCHAR2(30) PATH 'name',

            value_list XMLTYPE PATH 'value-list'

        ) n,

        XMLTABLE 

        ( 

          'value-list'

          PASSING n.value_list

          COLUMNS

            contract_status varchar2(30) PATH 'value[name="CONTRACT_STATUS"]/value',

            contract_status_desc varchar2(30) PATH 'value[name="CONTRACT_STATUS_DESC"]/value',

            source_system varchar2(30) PATH 'value[name="SOURCE_SYSTEM"]/value',

            source_value varchar2(30) PATH 'value[name="SOURCE_VALUE"]/value',

            source_desc varchar2(30) PATH 'value[name="SOURCE_DESC"]/value',

            visible varchar2(30) PATH 'value[name="VISIBLE"]/value',

            required varchar2(30) PATH 'value[name="REQUIRED"]/value'

        ) v;


which gives:



NAME                           CONTRACT_STATUS                CONTRACT_STATUS_DESC           SOURCE_SYSTEM                  SOURCE_VALUE                   SOURCE_DESC                    VISIBLE                        REQUIRED

------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------

7                              11xyzabc                       12xyzabc                       13xyzabc                       14xyzabc                       15xyzabc                       16xyzabc                       17xyzabc

7                              21xyzabc                       22xyzabc                       23xyzabc                       24xyzabc                       25xyzabc                       26xyzabc                       27xyzabc


You can then simply use that in an insert into ... (...) select ... statement.



If this is being used in PL/SQL, you could simply reference the xmltype variable in the initial XMLTABLE, rather than having to use the response subquery, but I expect you already knew that.






share|improve this answer





















  • FWIW, I think Alex's answer should be the right one; he demonstrates how you can just use a single XMLTABLE to get the results - the only difference is the path; the ones I've used are more compact than what Alex has used. If I were you, I'd switch to his answer, use the single XMLTABLE solution but maybe change the paths to the style I have used. YMMV, though, I guess.
    – Boneist
    Nov 21 '18 at 13:54



















2














If you had unique data you could potentially pivot what you have now. But you could also extract each column independently as part of the second XMLTable:



with ...

SELECT  n.att_name  AS name,

        v.*

FROM    response r,

        XMLTABLE

        (

          '//abc-values/attribute'

          PASSING r.xm

          COLUMNS

            att_name VARCHAR2(30) PATH 'name',

            value_list XMLTYPE PATH 'value-list'

        ) n,

        XMLTABLE 

        ( 

          'value-list'

          PASSING n.value_list

          COLUMNS

            contract_status varchar2(30)

              PATH 'value/name[text()="CONTRACT_STATUS"]/../value',

            contract_status_desc varchar2(30)

              PATH 'value/name[text()="CONTRACT_STATUS_DESC"]/../value',

            source_system varchar2(30)

              PATH 'value/name[text()="SOURCE_SYSTEM"]/../value',

            source_value varchar2(30)

              PATH 'value/name[text()="SOURCE_VALUE"]/../value',

            source_desc varchar2(30)

              PATH 'value/name[text()="SOURCE_DESC"]/../value',

            visible varchar2(30)

              PATH 'value/name[text()="VISIBLE"]/../value',

            required varchar2(30)

              PATH 'value/name[text()="REQUIRED"]/../value'

        ) v;



NAME                           CONTRACT_STATUS                CONTRACT_STATUS_DESC           SOURCE_SYSTEM                  SOURCE_VALUE                   SOURCE_DESC                    VISIBLE                        REQUIRED                      

------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------

7                              xyzabc                         xyzabc                         xyzabc                         xyzabc                         xyzabc                         xyzabc                         xyzabc                        

7                              xyzabc                         xyzabc                         xyzabc                         xyzabc                         xyzabc                         xyzabc                         xyzabc


or with a single XMLTable, assuming a single value-list per node:



SELECT  x.*

FROM    response r,

        XMLTABLE

        (

          '//abc-values/attribute'

          PASSING r.xm

          COLUMNS

            name VARCHAR2(30) PATH 'name',

            contract_status varchar2(30)

              PATH 'value-list/value/name[text()="CONTRACT_STATUS"]/../value',

            contract_status_desc varchar2(30)

              PATH 'value-list/value/name[text()="CONTRACT_STATUS_DESC"]/../value',

            source_system varchar2(30)

              PATH 'value-list/value/name[text()="SOURCE_SYSTEM"]/../value',

            source_value varchar2(30)

              PATH 'value-list/value/name[text()="SOURCE_VALUE"]/../value',

            source_desc varchar2(30)

              PATH 'value-list/value/name[text()="SOURCE_DESC"]/../value',

            visible varchar2(30)

              PATH 'value-list/value/name[text()="VISIBLE"]/../value',

            required varchar2(30)

              PATH 'value-list/value/name[text()="REQUIRED"]/../value'

        ) x;


To turn that into an insert, either select * from a subquery using that with clause, or embed the XML value into the XMLTable instead:



INSERT INTO some_table (name, contract_status, contract_status_desc,

  source_system, source_value, source_desc, visble, required)

SELECT  *

FROM    XMLTABLE

        (

          '//abc-values/attribute'

          PASSING xmltype('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soapenv:Body><tns:getAbcResponse xmlns:tns="http://xyz.abc.com"><abc-name>CONTRACT_STATUS</abc-name><abc-values>

<attribute><name>7</name><value-list><value><name>CONTRACT_STATUS</name><value>xyzabc</value></value><value><name>CONTRACT_STATUS_DESC</name><value>xyzabc</value></value><value><name>SOURCE_SYSTEM</name><value>xyzabc</value></value><value><name>SOURCE_VALUE</name><value>xyzabc</value></value><value><name>SOURCE_DESC</name><value>xyzabc</value></value><value><name>VISIBLE</name><value>xyzabc</value></value><value><name>REQUIRED</name><value>xyzabc</value></value></value-list></attribute>

<attribute><name>7</name><value-list><value><name>CONTRACT_STATUS</name><value>xyzabc</value></value><value><name>CONTRACT_STATUS_DESC</name><value>xyzabc</value></value><value><name>SOURCE_SYSTEM</name><value>xyzabc</value></value><value><name>SOURCE_VALUE</name><value>xyzabc</value></value><value><name>SOURCE_DESC</name><value>xyzabc</value></value><value><name>VISIBLE</name><value>xyzabc</value></value><value><name>REQUIRED</name><value>xyzabc</value></value></value-list></attribute>

</abc-values><status error-code="0"><error-msg>OK</error-msg></status></tns:getAbcResponse></soapenv:Body></soapenv:Envelope>')

          COLUMNS

            name VARCHAR2(30) PATH 'name',

            contract_status varchar2(30)

              PATH 'value-list/value/name[text()="CONTRACT_STATUS"]/../value',

            contract_status_desc varchar2(30)

              PATH 'value-list/value/name[text()="CONTRACT_STATUS_DESC"]/../value',

            source_system varchar2(30)

              PATH 'value-list/value/name[text()="SOURCE_SYSTEM"]/../value',

            source_value varchar2(30)

              PATH 'value-list/value/name[text()="SOURCE_VALUE"]/../value',

            source_desc varchar2(30)

              PATH 'value-list/value/name[text()="SOURCE_DESC"]/../value',

            visible varchar2(30)

              PATH 'value-list/value/name[text()="VISIBLE"]/../value',

            required varchar2(30)

              PATH 'value-list/value/name[text()="REQUIRED"]/../value'

        );


... or wherever you're really getting it from, of course.



The paths find value/name nodes with specific text values, and then find the adjacent value for each specific name. (@Boneist's syntax is effectively the same principle, but shorter and clearer; not sure why I made it so long! So you can use value[name="CONTRACT_STATUS"]/value in the first version above, and value-list/value[name="CONTRACT_STATUS"]/value in the second version using a single XMLTable. I'll leave mine as it is to show the difference...)



Also note that this approach lets you declare a suitable data type and size for each of the extracted column values, rather than having to use varchar2(30) for all of them. (I imagine required and visible and single-charcater flags, for instance).






share|improve this answer























  • I had to google to find the syntax I used... I ended up cribbing from my own question on the subject! How embarrassing... haha!
    – Boneist
    Nov 19 '18 at 15:35






  • 1




    I think I went the long way because I usually do that kind of filtering on attributes, and didn't think enough when I realised I couldn't use @ here. But I've lost track of the number of times I've searched for something I'm trying to do IRL and been half way through an answer before realising I wrote it. That's even more worrying...
    – Alex Poole
    Nov 19 '18 at 15:45











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53375965%2foracle-xml-to-insert-statement%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























2 Answers
2






active

oldest

votes








2 Answers
2






active

oldest

votes









active

oldest

votes






active

oldest

votes









2














Here's one way of selecting the data:



with response(xm) as ( select xmltype('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soapenv:Body><tns:getAbcResponse xmlns:tns="http://xyz.abc.com"><abc-name>CONTRACT_STATUS</abc-name><abc-values>

<attribute><name>7</name><value-list><value><name>CONTRACT_STATUS</name><value>11xyzabc</value></value><value><name>CONTRACT_STATUS_DESC</name><value>12xyzabc</value></value><value><name>SOURCE_SYSTEM</name><value>13xyzabc</value></value><value><name>SOURCE_VALUE</name><value>14xyzabc</value></value><value><name>SOURCE_DESC</name><value>15xyzabc</value></value><value><name>VISIBLE</name><value>16xyzabc</value></value><value><name>REQUIRED</name><value>17xyzabc</value></value></value-list></attribute>

<attribute><name>7</name><value-list><value><name>CONTRACT_STATUS</name><value>21xyzabc</value></value><value><name>CONTRACT_STATUS_DESC</name><value>22xyzabc</value></value><value><name>SOURCE_SYSTEM</name><value>23xyzabc</value></value><value><name>SOURCE_VALUE</name><value>24xyzabc</value></value><value><name>SOURCE_DESC</name><value>25xyzabc</value></value><value><name>VISIBLE</name><value>26xyzabc</value></value><value><name>REQUIRED</name><value>27xyzabc</value></value></value-list></attribute>

</abc-values><status error-code="0"><error-msg>OK</error-msg></status></tns:getAbcResponse></soapenv:Body></soapenv:Envelope>') from dual)

SELECT  n.att_name  AS name,

        v.contract_status,

        v.contract_status_desc,

        v.source_system,

        v.source_value,

        v.source_desc,

        v.visible,

        v.required

FROM    response r,

        XMLTABLE

        (

          '//abc-values/attribute'

          PASSING r.xm

          COLUMNS

            att_name VARCHAR2(30) PATH 'name',

            value_list XMLTYPE PATH 'value-list'

        ) n,

        XMLTABLE 

        ( 

          'value-list'

          PASSING n.value_list

          COLUMNS

            contract_status varchar2(30) PATH 'value[name="CONTRACT_STATUS"]/value',

            contract_status_desc varchar2(30) PATH 'value[name="CONTRACT_STATUS_DESC"]/value',

            source_system varchar2(30) PATH 'value[name="SOURCE_SYSTEM"]/value',

            source_value varchar2(30) PATH 'value[name="SOURCE_VALUE"]/value',

            source_desc varchar2(30) PATH 'value[name="SOURCE_DESC"]/value',

            visible varchar2(30) PATH 'value[name="VISIBLE"]/value',

            required varchar2(30) PATH 'value[name="REQUIRED"]/value'

        ) v;


which gives:



NAME                           CONTRACT_STATUS                CONTRACT_STATUS_DESC           SOURCE_SYSTEM                  SOURCE_VALUE                   SOURCE_DESC                    VISIBLE                        REQUIRED

------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------

7                              11xyzabc                       12xyzabc                       13xyzabc                       14xyzabc                       15xyzabc                       16xyzabc                       17xyzabc

7                              21xyzabc                       22xyzabc                       23xyzabc                       24xyzabc                       25xyzabc                       26xyzabc                       27xyzabc


You can then simply use that in an insert into ... (...) select ... statement.



If this is being used in PL/SQL, you could simply reference the xmltype variable in the initial XMLTABLE, rather than having to use the response subquery, but I expect you already knew that.






share|improve this answer





















  • FWIW, I think Alex's answer should be the right one; he demonstrates how you can just use a single XMLTABLE to get the results - the only difference is the path; the ones I've used are more compact than what Alex has used. If I were you, I'd switch to his answer, use the single XMLTABLE solution but maybe change the paths to the style I have used. YMMV, though, I guess.
    – Boneist
    Nov 21 '18 at 13:54
















2














Here's one way of selecting the data:



with response(xm) as ( select xmltype('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soapenv:Body><tns:getAbcResponse xmlns:tns="http://xyz.abc.com"><abc-name>CONTRACT_STATUS</abc-name><abc-values>

<attribute><name>7</name><value-list><value><name>CONTRACT_STATUS</name><value>11xyzabc</value></value><value><name>CONTRACT_STATUS_DESC</name><value>12xyzabc</value></value><value><name>SOURCE_SYSTEM</name><value>13xyzabc</value></value><value><name>SOURCE_VALUE</name><value>14xyzabc</value></value><value><name>SOURCE_DESC</name><value>15xyzabc</value></value><value><name>VISIBLE</name><value>16xyzabc</value></value><value><name>REQUIRED</name><value>17xyzabc</value></value></value-list></attribute>

<attribute><name>7</name><value-list><value><name>CONTRACT_STATUS</name><value>21xyzabc</value></value><value><name>CONTRACT_STATUS_DESC</name><value>22xyzabc</value></value><value><name>SOURCE_SYSTEM</name><value>23xyzabc</value></value><value><name>SOURCE_VALUE</name><value>24xyzabc</value></value><value><name>SOURCE_DESC</name><value>25xyzabc</value></value><value><name>VISIBLE</name><value>26xyzabc</value></value><value><name>REQUIRED</name><value>27xyzabc</value></value></value-list></attribute>

</abc-values><status error-code="0"><error-msg>OK</error-msg></status></tns:getAbcResponse></soapenv:Body></soapenv:Envelope>') from dual)

SELECT  n.att_name  AS name,

        v.contract_status,

        v.contract_status_desc,

        v.source_system,

        v.source_value,

        v.source_desc,

        v.visible,

        v.required

FROM    response r,

        XMLTABLE

        (

          '//abc-values/attribute'

          PASSING r.xm

          COLUMNS

            att_name VARCHAR2(30) PATH 'name',

            value_list XMLTYPE PATH 'value-list'

        ) n,

        XMLTABLE 

        ( 

          'value-list'

          PASSING n.value_list

          COLUMNS

            contract_status varchar2(30) PATH 'value[name="CONTRACT_STATUS"]/value',

            contract_status_desc varchar2(30) PATH 'value[name="CONTRACT_STATUS_DESC"]/value',

            source_system varchar2(30) PATH 'value[name="SOURCE_SYSTEM"]/value',

            source_value varchar2(30) PATH 'value[name="SOURCE_VALUE"]/value',

            source_desc varchar2(30) PATH 'value[name="SOURCE_DESC"]/value',

            visible varchar2(30) PATH 'value[name="VISIBLE"]/value',

            required varchar2(30) PATH 'value[name="REQUIRED"]/value'

        ) v;


which gives:



NAME                           CONTRACT_STATUS                CONTRACT_STATUS_DESC           SOURCE_SYSTEM                  SOURCE_VALUE                   SOURCE_DESC                    VISIBLE                        REQUIRED

------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------

7                              11xyzabc                       12xyzabc                       13xyzabc                       14xyzabc                       15xyzabc                       16xyzabc                       17xyzabc

7                              21xyzabc                       22xyzabc                       23xyzabc                       24xyzabc                       25xyzabc                       26xyzabc                       27xyzabc


You can then simply use that in an insert into ... (...) select ... statement.



If this is being used in PL/SQL, you could simply reference the xmltype variable in the initial XMLTABLE, rather than having to use the response subquery, but I expect you already knew that.






share|improve this answer





















  • FWIW, I think Alex's answer should be the right one; he demonstrates how you can just use a single XMLTABLE to get the results - the only difference is the path; the ones I've used are more compact than what Alex has used. If I were you, I'd switch to his answer, use the single XMLTABLE solution but maybe change the paths to the style I have used. YMMV, though, I guess.
    – Boneist
    Nov 21 '18 at 13:54














2












2








2






Here's one way of selecting the data:



with response(xm) as ( select xmltype('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soapenv:Body><tns:getAbcResponse xmlns:tns="http://xyz.abc.com"><abc-name>CONTRACT_STATUS</abc-name><abc-values>

<attribute><name>7</name><value-list><value><name>CONTRACT_STATUS</name><value>11xyzabc</value></value><value><name>CONTRACT_STATUS_DESC</name><value>12xyzabc</value></value><value><name>SOURCE_SYSTEM</name><value>13xyzabc</value></value><value><name>SOURCE_VALUE</name><value>14xyzabc</value></value><value><name>SOURCE_DESC</name><value>15xyzabc</value></value><value><name>VISIBLE</name><value>16xyzabc</value></value><value><name>REQUIRED</name><value>17xyzabc</value></value></value-list></attribute>

<attribute><name>7</name><value-list><value><name>CONTRACT_STATUS</name><value>21xyzabc</value></value><value><name>CONTRACT_STATUS_DESC</name><value>22xyzabc</value></value><value><name>SOURCE_SYSTEM</name><value>23xyzabc</value></value><value><name>SOURCE_VALUE</name><value>24xyzabc</value></value><value><name>SOURCE_DESC</name><value>25xyzabc</value></value><value><name>VISIBLE</name><value>26xyzabc</value></value><value><name>REQUIRED</name><value>27xyzabc</value></value></value-list></attribute>

</abc-values><status error-code="0"><error-msg>OK</error-msg></status></tns:getAbcResponse></soapenv:Body></soapenv:Envelope>') from dual)

SELECT  n.att_name  AS name,

        v.contract_status,

        v.contract_status_desc,

        v.source_system,

        v.source_value,

        v.source_desc,

        v.visible,

        v.required

FROM    response r,

        XMLTABLE

        (

          '//abc-values/attribute'

          PASSING r.xm

          COLUMNS

            att_name VARCHAR2(30) PATH 'name',

            value_list XMLTYPE PATH 'value-list'

        ) n,

        XMLTABLE 

        ( 

          'value-list'

          PASSING n.value_list

          COLUMNS

            contract_status varchar2(30) PATH 'value[name="CONTRACT_STATUS"]/value',

            contract_status_desc varchar2(30) PATH 'value[name="CONTRACT_STATUS_DESC"]/value',

            source_system varchar2(30) PATH 'value[name="SOURCE_SYSTEM"]/value',

            source_value varchar2(30) PATH 'value[name="SOURCE_VALUE"]/value',

            source_desc varchar2(30) PATH 'value[name="SOURCE_DESC"]/value',

            visible varchar2(30) PATH 'value[name="VISIBLE"]/value',

            required varchar2(30) PATH 'value[name="REQUIRED"]/value'

        ) v;


which gives:



NAME                           CONTRACT_STATUS                CONTRACT_STATUS_DESC           SOURCE_SYSTEM                  SOURCE_VALUE                   SOURCE_DESC                    VISIBLE                        REQUIRED

------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------

7                              11xyzabc                       12xyzabc                       13xyzabc                       14xyzabc                       15xyzabc                       16xyzabc                       17xyzabc

7                              21xyzabc                       22xyzabc                       23xyzabc                       24xyzabc                       25xyzabc                       26xyzabc                       27xyzabc


You can then simply use that in an insert into ... (...) select ... statement.



If this is being used in PL/SQL, you could simply reference the xmltype variable in the initial XMLTABLE, rather than having to use the response subquery, but I expect you already knew that.






share|improve this answer












Here's one way of selecting the data:



with response(xm) as ( select xmltype('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soapenv:Body><tns:getAbcResponse xmlns:tns="http://xyz.abc.com"><abc-name>CONTRACT_STATUS</abc-name><abc-values>

<attribute><name>7</name><value-list><value><name>CONTRACT_STATUS</name><value>11xyzabc</value></value><value><name>CONTRACT_STATUS_DESC</name><value>12xyzabc</value></value><value><name>SOURCE_SYSTEM</name><value>13xyzabc</value></value><value><name>SOURCE_VALUE</name><value>14xyzabc</value></value><value><name>SOURCE_DESC</name><value>15xyzabc</value></value><value><name>VISIBLE</name><value>16xyzabc</value></value><value><name>REQUIRED</name><value>17xyzabc</value></value></value-list></attribute>

<attribute><name>7</name><value-list><value><name>CONTRACT_STATUS</name><value>21xyzabc</value></value><value><name>CONTRACT_STATUS_DESC</name><value>22xyzabc</value></value><value><name>SOURCE_SYSTEM</name><value>23xyzabc</value></value><value><name>SOURCE_VALUE</name><value>24xyzabc</value></value><value><name>SOURCE_DESC</name><value>25xyzabc</value></value><value><name>VISIBLE</name><value>26xyzabc</value></value><value><name>REQUIRED</name><value>27xyzabc</value></value></value-list></attribute>

</abc-values><status error-code="0"><error-msg>OK</error-msg></status></tns:getAbcResponse></soapenv:Body></soapenv:Envelope>') from dual)

SELECT  n.att_name  AS name,

        v.contract_status,

        v.contract_status_desc,

        v.source_system,

        v.source_value,

        v.source_desc,

        v.visible,

        v.required

FROM    response r,

        XMLTABLE

        (

          '//abc-values/attribute'

          PASSING r.xm

          COLUMNS

            att_name VARCHAR2(30) PATH 'name',

            value_list XMLTYPE PATH 'value-list'

        ) n,

        XMLTABLE 

        ( 

          'value-list'

          PASSING n.value_list

          COLUMNS

            contract_status varchar2(30) PATH 'value[name="CONTRACT_STATUS"]/value',

            contract_status_desc varchar2(30) PATH 'value[name="CONTRACT_STATUS_DESC"]/value',

            source_system varchar2(30) PATH 'value[name="SOURCE_SYSTEM"]/value',

            source_value varchar2(30) PATH 'value[name="SOURCE_VALUE"]/value',

            source_desc varchar2(30) PATH 'value[name="SOURCE_DESC"]/value',

            visible varchar2(30) PATH 'value[name="VISIBLE"]/value',

            required varchar2(30) PATH 'value[name="REQUIRED"]/value'

        ) v;


which gives:



NAME                           CONTRACT_STATUS                CONTRACT_STATUS_DESC           SOURCE_SYSTEM                  SOURCE_VALUE                   SOURCE_DESC                    VISIBLE                        REQUIRED

------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------

7                              11xyzabc                       12xyzabc                       13xyzabc                       14xyzabc                       15xyzabc                       16xyzabc                       17xyzabc

7                              21xyzabc                       22xyzabc                       23xyzabc                       24xyzabc                       25xyzabc                       26xyzabc                       27xyzabc


You can then simply use that in an insert into ... (...) select ... statement.



If this is being used in PL/SQL, you could simply reference the xmltype variable in the initial XMLTABLE, rather than having to use the response subquery, but I expect you already knew that.







share|improve this answer












share|improve this answer



share|improve this answer










answered Nov 19 '18 at 15:07









Boneist

17.9k11028




17.9k11028












  • FWIW, I think Alex's answer should be the right one; he demonstrates how you can just use a single XMLTABLE to get the results - the only difference is the path; the ones I've used are more compact than what Alex has used. If I were you, I'd switch to his answer, use the single XMLTABLE solution but maybe change the paths to the style I have used. YMMV, though, I guess.
    – Boneist
    Nov 21 '18 at 13:54


















  • FWIW, I think Alex's answer should be the right one; he demonstrates how you can just use a single XMLTABLE to get the results - the only difference is the path; the ones I've used are more compact than what Alex has used. If I were you, I'd switch to his answer, use the single XMLTABLE solution but maybe change the paths to the style I have used. YMMV, though, I guess.
    – Boneist
    Nov 21 '18 at 13:54
















FWIW, I think Alex's answer should be the right one; he demonstrates how you can just use a single XMLTABLE to get the results - the only difference is the path; the ones I've used are more compact than what Alex has used. If I were you, I'd switch to his answer, use the single XMLTABLE solution but maybe change the paths to the style I have used. YMMV, though, I guess.
– Boneist
Nov 21 '18 at 13:54




FWIW, I think Alex's answer should be the right one; he demonstrates how you can just use a single XMLTABLE to get the results - the only difference is the path; the ones I've used are more compact than what Alex has used. If I were you, I'd switch to his answer, use the single XMLTABLE solution but maybe change the paths to the style I have used. YMMV, though, I guess.
– Boneist
Nov 21 '18 at 13:54













2














If you had unique data you could potentially pivot what you have now. But you could also extract each column independently as part of the second XMLTable:



with ...

SELECT  n.att_name  AS name,

        v.*

FROM    response r,

        XMLTABLE

        (

          '//abc-values/attribute'

          PASSING r.xm

          COLUMNS

            att_name VARCHAR2(30) PATH 'name',

            value_list XMLTYPE PATH 'value-list'

        ) n,

        XMLTABLE 

        ( 

          'value-list'

          PASSING n.value_list

          COLUMNS

            contract_status varchar2(30)

              PATH 'value/name[text()="CONTRACT_STATUS"]/../value',

            contract_status_desc varchar2(30)

              PATH 'value/name[text()="CONTRACT_STATUS_DESC"]/../value',

            source_system varchar2(30)

              PATH 'value/name[text()="SOURCE_SYSTEM"]/../value',

            source_value varchar2(30)

              PATH 'value/name[text()="SOURCE_VALUE"]/../value',

            source_desc varchar2(30)

              PATH 'value/name[text()="SOURCE_DESC"]/../value',

            visible varchar2(30)

              PATH 'value/name[text()="VISIBLE"]/../value',

            required varchar2(30)

              PATH 'value/name[text()="REQUIRED"]/../value'

        ) v;



NAME                           CONTRACT_STATUS                CONTRACT_STATUS_DESC           SOURCE_SYSTEM                  SOURCE_VALUE                   SOURCE_DESC                    VISIBLE                        REQUIRED                      

------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------

7                              xyzabc                         xyzabc                         xyzabc                         xyzabc                         xyzabc                         xyzabc                         xyzabc                        

7                              xyzabc                         xyzabc                         xyzabc                         xyzabc                         xyzabc                         xyzabc                         xyzabc


or with a single XMLTable, assuming a single value-list per node:



SELECT  x.*

FROM    response r,

        XMLTABLE

        (

          '//abc-values/attribute'

          PASSING r.xm

          COLUMNS

            name VARCHAR2(30) PATH 'name',

            contract_status varchar2(30)

              PATH 'value-list/value/name[text()="CONTRACT_STATUS"]/../value',

            contract_status_desc varchar2(30)

              PATH 'value-list/value/name[text()="CONTRACT_STATUS_DESC"]/../value',

            source_system varchar2(30)

              PATH 'value-list/value/name[text()="SOURCE_SYSTEM"]/../value',

            source_value varchar2(30)

              PATH 'value-list/value/name[text()="SOURCE_VALUE"]/../value',

            source_desc varchar2(30)

              PATH 'value-list/value/name[text()="SOURCE_DESC"]/../value',

            visible varchar2(30)

              PATH 'value-list/value/name[text()="VISIBLE"]/../value',

            required varchar2(30)

              PATH 'value-list/value/name[text()="REQUIRED"]/../value'

        ) x;


To turn that into an insert, either select * from a subquery using that with clause, or embed the XML value into the XMLTable instead:



INSERT INTO some_table (name, contract_status, contract_status_desc,

  source_system, source_value, source_desc, visble, required)

SELECT  *

FROM    XMLTABLE

        (

          '//abc-values/attribute'

          PASSING xmltype('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soapenv:Body><tns:getAbcResponse xmlns:tns="http://xyz.abc.com"><abc-name>CONTRACT_STATUS</abc-name><abc-values>

<attribute><name>7</name><value-list><value><name>CONTRACT_STATUS</name><value>xyzabc</value></value><value><name>CONTRACT_STATUS_DESC</name><value>xyzabc</value></value><value><name>SOURCE_SYSTEM</name><value>xyzabc</value></value><value><name>SOURCE_VALUE</name><value>xyzabc</value></value><value><name>SOURCE_DESC</name><value>xyzabc</value></value><value><name>VISIBLE</name><value>xyzabc</value></value><value><name>REQUIRED</name><value>xyzabc</value></value></value-list></attribute>

<attribute><name>7</name><value-list><value><name>CONTRACT_STATUS</name><value>xyzabc</value></value><value><name>CONTRACT_STATUS_DESC</name><value>xyzabc</value></value><value><name>SOURCE_SYSTEM</name><value>xyzabc</value></value><value><name>SOURCE_VALUE</name><value>xyzabc</value></value><value><name>SOURCE_DESC</name><value>xyzabc</value></value><value><name>VISIBLE</name><value>xyzabc</value></value><value><name>REQUIRED</name><value>xyzabc</value></value></value-list></attribute>

</abc-values><status error-code="0"><error-msg>OK</error-msg></status></tns:getAbcResponse></soapenv:Body></soapenv:Envelope>')

          COLUMNS

            name VARCHAR2(30) PATH 'name',

            contract_status varchar2(30)

              PATH 'value-list/value/name[text()="CONTRACT_STATUS"]/../value',

            contract_status_desc varchar2(30)

              PATH 'value-list/value/name[text()="CONTRACT_STATUS_DESC"]/../value',

            source_system varchar2(30)

              PATH 'value-list/value/name[text()="SOURCE_SYSTEM"]/../value',

            source_value varchar2(30)

              PATH 'value-list/value/name[text()="SOURCE_VALUE"]/../value',

            source_desc varchar2(30)

              PATH 'value-list/value/name[text()="SOURCE_DESC"]/../value',

            visible varchar2(30)

              PATH 'value-list/value/name[text()="VISIBLE"]/../value',

            required varchar2(30)

              PATH 'value-list/value/name[text()="REQUIRED"]/../value'

        );


... or wherever you're really getting it from, of course.



The paths find value/name nodes with specific text values, and then find the adjacent value for each specific name. (@Boneist's syntax is effectively the same principle, but shorter and clearer; not sure why I made it so long! So you can use value[name="CONTRACT_STATUS"]/value in the first version above, and value-list/value[name="CONTRACT_STATUS"]/value in the second version using a single XMLTable. I'll leave mine as it is to show the difference...)



Also note that this approach lets you declare a suitable data type and size for each of the extracted column values, rather than having to use varchar2(30) for all of them. (I imagine required and visible and single-charcater flags, for instance).






share|improve this answer























  • I had to google to find the syntax I used... I ended up cribbing from my own question on the subject! How embarrassing... haha!
    – Boneist
    Nov 19 '18 at 15:35






  • 1




    I think I went the long way because I usually do that kind of filtering on attributes, and didn't think enough when I realised I couldn't use @ here. But I've lost track of the number of times I've searched for something I'm trying to do IRL and been half way through an answer before realising I wrote it. That's even more worrying...
    – Alex Poole
    Nov 19 '18 at 15:45
















2














If you had unique data you could potentially pivot what you have now. But you could also extract each column independently as part of the second XMLTable:



with ...

SELECT  n.att_name  AS name,

        v.*

FROM    response r,

        XMLTABLE

        (

          '//abc-values/attribute'

          PASSING r.xm

          COLUMNS

            att_name VARCHAR2(30) PATH 'name',

            value_list XMLTYPE PATH 'value-list'

        ) n,

        XMLTABLE 

        ( 

          'value-list'

          PASSING n.value_list

          COLUMNS

            contract_status varchar2(30)

              PATH 'value/name[text()="CONTRACT_STATUS"]/../value',

            contract_status_desc varchar2(30)

              PATH 'value/name[text()="CONTRACT_STATUS_DESC"]/../value',

            source_system varchar2(30)

              PATH 'value/name[text()="SOURCE_SYSTEM"]/../value',

            source_value varchar2(30)

              PATH 'value/name[text()="SOURCE_VALUE"]/../value',

            source_desc varchar2(30)

              PATH 'value/name[text()="SOURCE_DESC"]/../value',

            visible varchar2(30)

              PATH 'value/name[text()="VISIBLE"]/../value',

            required varchar2(30)

              PATH 'value/name[text()="REQUIRED"]/../value'

        ) v;



NAME                           CONTRACT_STATUS                CONTRACT_STATUS_DESC           SOURCE_SYSTEM                  SOURCE_VALUE                   SOURCE_DESC                    VISIBLE                        REQUIRED                      

------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------

7                              xyzabc                         xyzabc                         xyzabc                         xyzabc                         xyzabc                         xyzabc                         xyzabc                        

7                              xyzabc                         xyzabc                         xyzabc                         xyzabc                         xyzabc                         xyzabc                         xyzabc


or with a single XMLTable, assuming a single value-list per node:



SELECT  x.*

FROM    response r,

        XMLTABLE

        (

          '//abc-values/attribute'

          PASSING r.xm

          COLUMNS

            name VARCHAR2(30) PATH 'name',

            contract_status varchar2(30)

              PATH 'value-list/value/name[text()="CONTRACT_STATUS"]/../value',

            contract_status_desc varchar2(30)

              PATH 'value-list/value/name[text()="CONTRACT_STATUS_DESC"]/../value',

            source_system varchar2(30)

              PATH 'value-list/value/name[text()="SOURCE_SYSTEM"]/../value',

            source_value varchar2(30)

              PATH 'value-list/value/name[text()="SOURCE_VALUE"]/../value',

            source_desc varchar2(30)

              PATH 'value-list/value/name[text()="SOURCE_DESC"]/../value',

            visible varchar2(30)

              PATH 'value-list/value/name[text()="VISIBLE"]/../value',

            required varchar2(30)

              PATH 'value-list/value/name[text()="REQUIRED"]/../value'

        ) x;


To turn that into an insert, either select * from a subquery using that with clause, or embed the XML value into the XMLTable instead:



INSERT INTO some_table (name, contract_status, contract_status_desc,

  source_system, source_value, source_desc, visble, required)

SELECT  *

FROM    XMLTABLE

        (

          '//abc-values/attribute'

          PASSING xmltype('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soapenv:Body><tns:getAbcResponse xmlns:tns="http://xyz.abc.com"><abc-name>CONTRACT_STATUS</abc-name><abc-values>

<attribute><name>7</name><value-list><value><name>CONTRACT_STATUS</name><value>xyzabc</value></value><value><name>CONTRACT_STATUS_DESC</name><value>xyzabc</value></value><value><name>SOURCE_SYSTEM</name><value>xyzabc</value></value><value><name>SOURCE_VALUE</name><value>xyzabc</value></value><value><name>SOURCE_DESC</name><value>xyzabc</value></value><value><name>VISIBLE</name><value>xyzabc</value></value><value><name>REQUIRED</name><value>xyzabc</value></value></value-list></attribute>

<attribute><name>7</name><value-list><value><name>CONTRACT_STATUS</name><value>xyzabc</value></value><value><name>CONTRACT_STATUS_DESC</name><value>xyzabc</value></value><value><name>SOURCE_SYSTEM</name><value>xyzabc</value></value><value><name>SOURCE_VALUE</name><value>xyzabc</value></value><value><name>SOURCE_DESC</name><value>xyzabc</value></value><value><name>VISIBLE</name><value>xyzabc</value></value><value><name>REQUIRED</name><value>xyzabc</value></value></value-list></attribute>

</abc-values><status error-code="0"><error-msg>OK</error-msg></status></tns:getAbcResponse></soapenv:Body></soapenv:Envelope>')

          COLUMNS

            name VARCHAR2(30) PATH 'name',

            contract_status varchar2(30)

              PATH 'value-list/value/name[text()="CONTRACT_STATUS"]/../value',

            contract_status_desc varchar2(30)

              PATH 'value-list/value/name[text()="CONTRACT_STATUS_DESC"]/../value',

            source_system varchar2(30)

              PATH 'value-list/value/name[text()="SOURCE_SYSTEM"]/../value',

            source_value varchar2(30)

              PATH 'value-list/value/name[text()="SOURCE_VALUE"]/../value',

            source_desc varchar2(30)

              PATH 'value-list/value/name[text()="SOURCE_DESC"]/../value',

            visible varchar2(30)

              PATH 'value-list/value/name[text()="VISIBLE"]/../value',

            required varchar2(30)

              PATH 'value-list/value/name[text()="REQUIRED"]/../value'

        );


... or wherever you're really getting it from, of course.



The paths find value/name nodes with specific text values, and then find the adjacent value for each specific name. (@Boneist's syntax is effectively the same principle, but shorter and clearer; not sure why I made it so long! So you can use value[name="CONTRACT_STATUS"]/value in the first version above, and value-list/value[name="CONTRACT_STATUS"]/value in the second version using a single XMLTable. I'll leave mine as it is to show the difference...)



Also note that this approach lets you declare a suitable data type and size for each of the extracted column values, rather than having to use varchar2(30) for all of them. (I imagine required and visible and single-charcater flags, for instance).






share|improve this answer























  • I had to google to find the syntax I used... I ended up cribbing from my own question on the subject! How embarrassing... haha!
    – Boneist
    Nov 19 '18 at 15:35






  • 1




    I think I went the long way because I usually do that kind of filtering on attributes, and didn't think enough when I realised I couldn't use @ here. But I've lost track of the number of times I've searched for something I'm trying to do IRL and been half way through an answer before realising I wrote it. That's even more worrying...
    – Alex Poole
    Nov 19 '18 at 15:45














2












2








2






If you had unique data you could potentially pivot what you have now. But you could also extract each column independently as part of the second XMLTable:



with ...

SELECT  n.att_name  AS name,

        v.*

FROM    response r,

        XMLTABLE

        (

          '//abc-values/attribute'

          PASSING r.xm

          COLUMNS

            att_name VARCHAR2(30) PATH 'name',

            value_list XMLTYPE PATH 'value-list'

        ) n,

        XMLTABLE 

        ( 

          'value-list'

          PASSING n.value_list

          COLUMNS

            contract_status varchar2(30)

              PATH 'value/name[text()="CONTRACT_STATUS"]/../value',

            contract_status_desc varchar2(30)

              PATH 'value/name[text()="CONTRACT_STATUS_DESC"]/../value',

            source_system varchar2(30)

              PATH 'value/name[text()="SOURCE_SYSTEM"]/../value',

            source_value varchar2(30)

              PATH 'value/name[text()="SOURCE_VALUE"]/../value',

            source_desc varchar2(30)

              PATH 'value/name[text()="SOURCE_DESC"]/../value',

            visible varchar2(30)

              PATH 'value/name[text()="VISIBLE"]/../value',

            required varchar2(30)

              PATH 'value/name[text()="REQUIRED"]/../value'

        ) v;



NAME                           CONTRACT_STATUS                CONTRACT_STATUS_DESC           SOURCE_SYSTEM                  SOURCE_VALUE                   SOURCE_DESC                    VISIBLE                        REQUIRED                      

------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------

7                              xyzabc                         xyzabc                         xyzabc                         xyzabc                         xyzabc                         xyzabc                         xyzabc                        

7                              xyzabc                         xyzabc                         xyzabc                         xyzabc                         xyzabc                         xyzabc                         xyzabc


or with a single XMLTable, assuming a single value-list per node:



SELECT  x.*

FROM    response r,

        XMLTABLE

        (

          '//abc-values/attribute'

          PASSING r.xm

          COLUMNS

            name VARCHAR2(30) PATH 'name',

            contract_status varchar2(30)

              PATH 'value-list/value/name[text()="CONTRACT_STATUS"]/../value',

            contract_status_desc varchar2(30)

              PATH 'value-list/value/name[text()="CONTRACT_STATUS_DESC"]/../value',

            source_system varchar2(30)

              PATH 'value-list/value/name[text()="SOURCE_SYSTEM"]/../value',

            source_value varchar2(30)

              PATH 'value-list/value/name[text()="SOURCE_VALUE"]/../value',

            source_desc varchar2(30)

              PATH 'value-list/value/name[text()="SOURCE_DESC"]/../value',

            visible varchar2(30)

              PATH 'value-list/value/name[text()="VISIBLE"]/../value',

            required varchar2(30)

              PATH 'value-list/value/name[text()="REQUIRED"]/../value'

        ) x;


To turn that into an insert, either select * from a subquery using that with clause, or embed the XML value into the XMLTable instead:



INSERT INTO some_table (name, contract_status, contract_status_desc,

  source_system, source_value, source_desc, visble, required)

SELECT  *

FROM    XMLTABLE

        (

          '//abc-values/attribute'

          PASSING xmltype('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soapenv:Body><tns:getAbcResponse xmlns:tns="http://xyz.abc.com"><abc-name>CONTRACT_STATUS</abc-name><abc-values>

<attribute><name>7</name><value-list><value><name>CONTRACT_STATUS</name><value>xyzabc</value></value><value><name>CONTRACT_STATUS_DESC</name><value>xyzabc</value></value><value><name>SOURCE_SYSTEM</name><value>xyzabc</value></value><value><name>SOURCE_VALUE</name><value>xyzabc</value></value><value><name>SOURCE_DESC</name><value>xyzabc</value></value><value><name>VISIBLE</name><value>xyzabc</value></value><value><name>REQUIRED</name><value>xyzabc</value></value></value-list></attribute>

<attribute><name>7</name><value-list><value><name>CONTRACT_STATUS</name><value>xyzabc</value></value><value><name>CONTRACT_STATUS_DESC</name><value>xyzabc</value></value><value><name>SOURCE_SYSTEM</name><value>xyzabc</value></value><value><name>SOURCE_VALUE</name><value>xyzabc</value></value><value><name>SOURCE_DESC</name><value>xyzabc</value></value><value><name>VISIBLE</name><value>xyzabc</value></value><value><name>REQUIRED</name><value>xyzabc</value></value></value-list></attribute>

</abc-values><status error-code="0"><error-msg>OK</error-msg></status></tns:getAbcResponse></soapenv:Body></soapenv:Envelope>')

          COLUMNS

            name VARCHAR2(30) PATH 'name',

            contract_status varchar2(30)

              PATH 'value-list/value/name[text()="CONTRACT_STATUS"]/../value',

            contract_status_desc varchar2(30)

              PATH 'value-list/value/name[text()="CONTRACT_STATUS_DESC"]/../value',

            source_system varchar2(30)

              PATH 'value-list/value/name[text()="SOURCE_SYSTEM"]/../value',

            source_value varchar2(30)

              PATH 'value-list/value/name[text()="SOURCE_VALUE"]/../value',

            source_desc varchar2(30)

              PATH 'value-list/value/name[text()="SOURCE_DESC"]/../value',

            visible varchar2(30)

              PATH 'value-list/value/name[text()="VISIBLE"]/../value',

            required varchar2(30)

              PATH 'value-list/value/name[text()="REQUIRED"]/../value'

        );


... or wherever you're really getting it from, of course.



The paths find value/name nodes with specific text values, and then find the adjacent value for each specific name. (@Boneist's syntax is effectively the same principle, but shorter and clearer; not sure why I made it so long! So you can use value[name="CONTRACT_STATUS"]/value in the first version above, and value-list/value[name="CONTRACT_STATUS"]/value in the second version using a single XMLTable. I'll leave mine as it is to show the difference...)



Also note that this approach lets you declare a suitable data type and size for each of the extracted column values, rather than having to use varchar2(30) for all of them. (I imagine required and visible and single-charcater flags, for instance).






share|improve this answer














If you had unique data you could potentially pivot what you have now. But you could also extract each column independently as part of the second XMLTable:



with ...

SELECT  n.att_name  AS name,

        v.*

FROM    response r,

        XMLTABLE

        (

          '//abc-values/attribute'

          PASSING r.xm

          COLUMNS

            att_name VARCHAR2(30) PATH 'name',

            value_list XMLTYPE PATH 'value-list'

        ) n,

        XMLTABLE 

        ( 

          'value-list'

          PASSING n.value_list

          COLUMNS

            contract_status varchar2(30)

              PATH 'value/name[text()="CONTRACT_STATUS"]/../value',

            contract_status_desc varchar2(30)

              PATH 'value/name[text()="CONTRACT_STATUS_DESC"]/../value',

            source_system varchar2(30)

              PATH 'value/name[text()="SOURCE_SYSTEM"]/../value',

            source_value varchar2(30)

              PATH 'value/name[text()="SOURCE_VALUE"]/../value',

            source_desc varchar2(30)

              PATH 'value/name[text()="SOURCE_DESC"]/../value',

            visible varchar2(30)

              PATH 'value/name[text()="VISIBLE"]/../value',

            required varchar2(30)

              PATH 'value/name[text()="REQUIRED"]/../value'

        ) v;



NAME                           CONTRACT_STATUS                CONTRACT_STATUS_DESC           SOURCE_SYSTEM                  SOURCE_VALUE                   SOURCE_DESC                    VISIBLE                        REQUIRED                      

------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------

7                              xyzabc                         xyzabc                         xyzabc                         xyzabc                         xyzabc                         xyzabc                         xyzabc                        

7                              xyzabc                         xyzabc                         xyzabc                         xyzabc                         xyzabc                         xyzabc                         xyzabc


or with a single XMLTable, assuming a single value-list per node:



SELECT  x.*

FROM    response r,

        XMLTABLE

        (

          '//abc-values/attribute'

          PASSING r.xm

          COLUMNS

            name VARCHAR2(30) PATH 'name',

            contract_status varchar2(30)

              PATH 'value-list/value/name[text()="CONTRACT_STATUS"]/../value',

            contract_status_desc varchar2(30)

              PATH 'value-list/value/name[text()="CONTRACT_STATUS_DESC"]/../value',

            source_system varchar2(30)

              PATH 'value-list/value/name[text()="SOURCE_SYSTEM"]/../value',

            source_value varchar2(30)

              PATH 'value-list/value/name[text()="SOURCE_VALUE"]/../value',

            source_desc varchar2(30)

              PATH 'value-list/value/name[text()="SOURCE_DESC"]/../value',

            visible varchar2(30)

              PATH 'value-list/value/name[text()="VISIBLE"]/../value',

            required varchar2(30)

              PATH 'value-list/value/name[text()="REQUIRED"]/../value'

        ) x;


To turn that into an insert, either select * from a subquery using that with clause, or embed the XML value into the XMLTable instead:



INSERT INTO some_table (name, contract_status, contract_status_desc,

  source_system, source_value, source_desc, visble, required)

SELECT  *

FROM    XMLTABLE

        (

          '//abc-values/attribute'

          PASSING xmltype('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soapenv:Body><tns:getAbcResponse xmlns:tns="http://xyz.abc.com"><abc-name>CONTRACT_STATUS</abc-name><abc-values>

<attribute><name>7</name><value-list><value><name>CONTRACT_STATUS</name><value>xyzabc</value></value><value><name>CONTRACT_STATUS_DESC</name><value>xyzabc</value></value><value><name>SOURCE_SYSTEM</name><value>xyzabc</value></value><value><name>SOURCE_VALUE</name><value>xyzabc</value></value><value><name>SOURCE_DESC</name><value>xyzabc</value></value><value><name>VISIBLE</name><value>xyzabc</value></value><value><name>REQUIRED</name><value>xyzabc</value></value></value-list></attribute>

<attribute><name>7</name><value-list><value><name>CONTRACT_STATUS</name><value>xyzabc</value></value><value><name>CONTRACT_STATUS_DESC</name><value>xyzabc</value></value><value><name>SOURCE_SYSTEM</name><value>xyzabc</value></value><value><name>SOURCE_VALUE</name><value>xyzabc</value></value><value><name>SOURCE_DESC</name><value>xyzabc</value></value><value><name>VISIBLE</name><value>xyzabc</value></value><value><name>REQUIRED</name><value>xyzabc</value></value></value-list></attribute>

</abc-values><status error-code="0"><error-msg>OK</error-msg></status></tns:getAbcResponse></soapenv:Body></soapenv:Envelope>')

          COLUMNS

            name VARCHAR2(30) PATH 'name',

            contract_status varchar2(30)

              PATH 'value-list/value/name[text()="CONTRACT_STATUS"]/../value',

            contract_status_desc varchar2(30)

              PATH 'value-list/value/name[text()="CONTRACT_STATUS_DESC"]/../value',

            source_system varchar2(30)

              PATH 'value-list/value/name[text()="SOURCE_SYSTEM"]/../value',

            source_value varchar2(30)

              PATH 'value-list/value/name[text()="SOURCE_VALUE"]/../value',

            source_desc varchar2(30)

              PATH 'value-list/value/name[text()="SOURCE_DESC"]/../value',

            visible varchar2(30)

              PATH 'value-list/value/name[text()="VISIBLE"]/../value',

            required varchar2(30)

              PATH 'value-list/value/name[text()="REQUIRED"]/../value'

        );


... or wherever you're really getting it from, of course.



The paths find value/name nodes with specific text values, and then find the adjacent value for each specific name. (@Boneist's syntax is effectively the same principle, but shorter and clearer; not sure why I made it so long! So you can use value[name="CONTRACT_STATUS"]/value in the first version above, and value-list/value[name="CONTRACT_STATUS"]/value in the second version using a single XMLTable. I'll leave mine as it is to show the difference...)



Also note that this approach lets you declare a suitable data type and size for each of the extracted column values, rather than having to use varchar2(30) for all of them. (I imagine required and visible and single-charcater flags, for instance).







share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 19 '18 at 15:19

























answered Nov 19 '18 at 15:06









Alex Poole

129k6101176




129k6101176












  • I had to google to find the syntax I used... I ended up cribbing from my own question on the subject! How embarrassing... haha!
    – Boneist
    Nov 19 '18 at 15:35






  • 1




    I think I went the long way because I usually do that kind of filtering on attributes, and didn't think enough when I realised I couldn't use @ here. But I've lost track of the number of times I've searched for something I'm trying to do IRL and been half way through an answer before realising I wrote it. That's even more worrying...
    – Alex Poole
    Nov 19 '18 at 15:45


















  • I had to google to find the syntax I used... I ended up cribbing from my own question on the subject! How embarrassing... haha!
    – Boneist
    Nov 19 '18 at 15:35






  • 1




    I think I went the long way because I usually do that kind of filtering on attributes, and didn't think enough when I realised I couldn't use @ here. But I've lost track of the number of times I've searched for something I'm trying to do IRL and been half way through an answer before realising I wrote it. That's even more worrying...
    – Alex Poole
    Nov 19 '18 at 15:45
















I had to google to find the syntax I used... I ended up cribbing from my own question on the subject! How embarrassing... haha!
– Boneist
Nov 19 '18 at 15:35




I had to google to find the syntax I used... I ended up cribbing from my own question on the subject! How embarrassing... haha!
– Boneist
Nov 19 '18 at 15:35




1




1




I think I went the long way because I usually do that kind of filtering on attributes, and didn't think enough when I realised I couldn't use @ here. But I've lost track of the number of times I've searched for something I'm trying to do IRL and been half way through an answer before realising I wrote it. That's even more worrying...
– Alex Poole
Nov 19 '18 at 15:45




I think I went the long way because I usually do that kind of filtering on attributes, and didn't think enough when I realised I couldn't use @ here. But I've lost track of the number of times I've searched for something I'm trying to do IRL and been half way through an answer before realising I wrote it. That's even more worrying...
– Alex Poole
Nov 19 '18 at 15:45


















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid …



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.





Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


Please pay close attention to the following guidance:


  • Please be sure to answer the question. Provide details and share your research!

But avoid …



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53375965%2foracle-xml-to-insert-statement%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

Can a sorcerer learn a 5th-level spell early by creating spell slots using the Font of Magic feature?

Image
28 6 $begingroup$ Per the Font of Magic feature, sorcerer can use Flexible Casting to create 5th-level spell slots at level 7, even though they are not typically available until level 9. You can transform unexpended sorcery points into one spell slot as a bonus action on your turn. The Creating Spell Slots table shows the cost of creating a spell slot of [5th level is 7] If such a sorcerer levels up while still having this 5th-level spell slot, can they choose a 5th-level spell as the spell they gain upon levelling up? The Spellcasting feature states: Additionally, when you gain a level in this class, you can choose one of the sorcerer spells you know and replace it with another spell from the sorcerer spell list, which also must be of a level for which you have spell slots.
Read more

Does disintegrating a polymorphed enemy still kill it after the 2018 errata?

Image
up vote 13 down vote favorite 1 After the 2018 errata, the disintegrate spell description now reads: A creature targeted by this spell must make a Dexterity saving throw. On a failed save, the target takes 10d6 + 40 force damage. The target is disintegrated if this damage leaves it with 0 hit points. If you were to polymorph an enemy into a rat and then disintegrate it, would the enemy be disintegrated or would it just return to its original form? I know that for Druids, it’s not an instant kill anymore, but is this the case for polymorph as well? dnd-5e spells polymorph errata share | improve this question edited 18 hours ago
Read more

A Topological Invariant for $pi_3(U(n))$

Image
3 3 $begingroup$ Recently, I saw a construction of topological invariant for $pi_3(U(n))$ with $ngeq 2$ : $$ N=frac{1}{24pi^2}int_{S^3} d^3x epsilon^{ijk} Tr[(U^{-1}partial_{x_i}U)(U^{-1}partial_{x_j}U)(U^{-1}partial_{x_k}U)] , $$ where $Uin U(n)$ depends on $boldsymbol{x}=(x_1,x_2,x_3)in S^3$ , $epsilon^{ijk}$ is the Levi-Civita symbol, $i,j,k=1,2,3$ , and the duplicated indexes are summed over. It is claimed that $N$ is an integer, but why? Update 02/02/2019 I think I got an argument for $n=2$ . In this case, $U=e^{i varphi} q$ with $qin SU(2)$ . Due to the trace and the Levi-Civita symbol in $N$ , $varphi$ does not contribute to $N$ . As $Tr[q^{dagger}partial_i q]=0$ and $(q^{dagger}partial_i q)^{dagger}=-q^{dagger}partial_i q$ , $q^{dagger}partial_i q$ in geneeral has the form $q^{dagger}partia
Read more