Mixing
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:
- get first 7 rows from this query,
- transform into single insert query -> execute / put into collection
- get next 7 rows from this query
- etc.
In other way:
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
asked Nov 19 '18 at 13:44
q4za4
465
add a comment |
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:
- get first 7 rows from this query,
- transform into single insert query -> execute / put into collection
- get next 7 rows from this query
- etc.
In other way:
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
asked Nov 19 '18 at 13:44
q4za4
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
add a comment |
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:
- get first 7 rows from this query,
- transform into single insert query -> execute / put into collection
- get next 7 rows from this query
- etc.
In other way:
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
asked Nov 19 '18 at 13:44
q4za4
465
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:
- get first 7 rows from this query,
- transform into single insert query -> execute / put into collection
- get next 7 rows from this query
- etc.
In other way:
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
asked Nov 19 '18 at 13:44
q4za4
465
asked Nov 19 '18 at 13:44
q4za4
465
edited Nov 19 '18 at 13:55
asked Nov 19 '18 at 13:44
q4za4
465
asked Nov 19 '18 at 13:44
q4za4
465
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
add a comment |
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
add a comment |
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.
answered Nov 19 '18 at 15:07
Boneist
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
add a comment |
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).
answered Nov 19 '18 at 15:06
Alex Poole
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
add a comment |
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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.
answered Nov 19 '18 at 15:07
Boneist
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
add a comment |
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.
answered Nov 19 '18 at 15:07
Boneist
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
add a comment |
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.
answered Nov 19 '18 at 15:07
Boneist
17.9k11028
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.
answered Nov 19 '18 at 15:07
Boneist
17.9k11028
answered Nov 19 '18 at 15:07
Boneist
17.9k11028
answered Nov 19 '18 at 15:07
Boneist
17.9k11028
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
add a comment |
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
add a comment |
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).
answered Nov 19 '18 at 15:06
Alex Poole
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
add a comment |
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).
answered Nov 19 '18 at 15:06
Alex Poole
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
add a comment |
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).
answered Nov 19 '18 at 15:06
Alex Poole
129k6101176
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).
answered Nov 19 '18 at 15:06
Alex Poole
129k6101176
edited Nov 19 '18 at 15:19
answered Nov 19 '18 at 15:06
Alex Poole
129k6101176
answered Nov 19 '18 at 15:06
Alex Poole
129k6101176
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
add a comment |
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
add a comment |
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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