Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 SOAP extract  [new]
dmdmdm
Member

Откуда: Нижний Новгород
Сообщений: 1837
Поднял свои упражнения по XML.

Упростил до такого.

+
with t as
 (select xmltype('<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope">
  <soap:Body>
    <m:GETResponse xmlns:m="http://localhost/NOXT/">
      <return xmlns:xs="http://www.w3.org/2001/XMLSchema"
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
          {return}
      </return>
    </m:GETResponse>
  </soap:Body>
</soap:Envelope>') xml
    from dual)
select x.*
  from t,
       XMLTABLE('//return' PASSING t.xml COLUMNS xml VARCHAR2(400) path '/*') x;


Что-то выдает.

Но изначальный объект вот такой.

+
<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope">
	<soap:Body>
		<m:GETResponse xmlns:m="http://localhost/NOXT/">
			<m:return xmlns:xs="http://www.w3.org/2001/XMLSchema"
					xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">{
	"object": {
		"inf_prices": [
			{
				"ID_WARE": "12a14479-a2a3-11eb-8c3d-a8a159650ac5",
				"PRICE1": 0
			},
			{
				"ID_WARE": "a3f8c04b-a7f9-11eb-8c3d-a8a159650ac5",
				"PRICE1": 15000
			},
			{
				"ID_WARE": "8615a769-c52b-11eb-8c3d-a8a159650ac5",
				"PRICE1": 0
			},
			{
				"ID_WARE": "5a7ebf47-d8ad-11eb-8c3f-a8a159650ac5",
				"PRICE1": 1500
			},
			{
				"ID_WARE": "4a3a4fb7-1479-11ec-8c49-a8a159650ac5",
				"PRICE1": 1100
			},
			{
				"ID_WARE": "7c07335c-16e6-11ec-8c49-a8a159650ac5",
				"PRICE1": 1100
			},
			{
				"ID_WARE": "969031c3-1a09-11ec-8c4a-a8a159650ac5",
				"PRICE1": 1200
			},
			{
				"ID_WARE": "888893be-1c55-11ec-8c4a-a8a159650ac5",
				"PRICE1": 1500
			},
			{
				"ID_WARE": "45c7aef1-1c64-11ec-8c4a-a8a159650ac5",
				"PRICE1": 0
			},
			{
				"ID_WARE": "58db63cc-1c64-11ec-8c4a-a8a159650ac5",
				"PRICE1": 0
			},
			{
				"ID_WARE": "a97fbd7d-1f66-11ec-8c4a-a8a159650ac5",
				"PRICE1": 1200
			},
			{
				"ID_WARE": "de526b65-42e8-11ec-8c4d-a8a159650ac5",
				"PRICE1": 1100
			},
			{
				"ID_WARE": "65d55968-92b6-11eb-9524-181deaf1436f",
				"PRICE1": 1300
			},
			{
				"ID_WARE": "cda12b0d-8ade-11eb-9670-04d4c4aa3454",
				"PRICE1": 1260
			},
			{
				"ID_WARE": "5a9afc42-8b1c-11eb-9671-04d4c4aa3454",
				"PRICE1": 0
			},
			{
				"ID_WARE": "bcd19d0d-8d42-11eb-9671-04d4c4aa3454",
				"PRICE1": 12600
			},
			{
				"ID_WARE": "598faa9f-983d-11eb-9674-04d4c4aa3454",
				"PRICE1": 1500
			},
			{
				"ID_WARE": "1c77169e-9b73-11eb-9674-04d4c4aa3454",
				"PRICE1": 1000
			},
			{
				"ID_WARE": "1c77169f-9b73-11eb-9674-04d4c4aa3454",
				"PRICE1": 1500
			}
		]
	}
}</m:return>
		</m:GETResponse>
	</soap:Body>
</soap:Envelope>


Направьте, пожалуйста, на нужный путь.

Нужен какой-то пакет SOAP-парсинга, чтобы достать JSON.
24 ноя 21, 14:59    [22400222]     Ответить | Цитировать Сообщить модератору
 Re: SOAP extract  [new]
dmdmdm
Member

Откуда: Нижний Новгород
Сообщений: 1837
Версия 18 XE.
24 ноя 21, 15:01    [22400224]     Ответить | Цитировать Сообщить модератору
 Re: SOAP extract  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 19924
with t as (select '<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope">
  <soap:Body>
    <m:GETResponse xmlns:m="http://localhost/NOXT/">
      <m:return xmlns:xs="http://www.w3.org/2001/XMLSchema"
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">{
  "object": {
    "inf_prices": [
      {
        "ID_WARE": "12a14479-a2a3-11eb-8c3d-a8a159650ac5",
        "PRICE1": 0
      },
      {
        "ID_WARE": "a3f8c04b-a7f9-11eb-8c3d-a8a159650ac5",
        "PRICE1": 15000
      },
      {
        "ID_WARE": "8615a769-c52b-11eb-8c3d-a8a159650ac5",
        "PRICE1": 0
      },
      {
        "ID_WARE": "5a7ebf47-d8ad-11eb-8c3f-a8a159650ac5",
        "PRICE1": 1500
      },
      {
        "ID_WARE": "4a3a4fb7-1479-11ec-8c49-a8a159650ac5",
        "PRICE1": 1100
      },
      {
        "ID_WARE": "7c07335c-16e6-11ec-8c49-a8a159650ac5",
        "PRICE1": 1100
      },
      {
        "ID_WARE": "969031c3-1a09-11ec-8c4a-a8a159650ac5",
        "PRICE1": 1200
      },
      {
        "ID_WARE": "888893be-1c55-11ec-8c4a-a8a159650ac5",
        "PRICE1": 1500
      },
      {
        "ID_WARE": "45c7aef1-1c64-11ec-8c4a-a8a159650ac5",
        "PRICE1": 0
      },
      {
        "ID_WARE": "58db63cc-1c64-11ec-8c4a-a8a159650ac5",
        "PRICE1": 0
      },
      {
        "ID_WARE": "a97fbd7d-1f66-11ec-8c4a-a8a159650ac5",
        "PRICE1": 1200
      },
      {
        "ID_WARE": "de526b65-42e8-11ec-8c4d-a8a159650ac5",
        "PRICE1": 1100
      },
      {
        "ID_WARE": "65d55968-92b6-11eb-9524-181deaf1436f",
        "PRICE1": 1300
      },
      {
        "ID_WARE": "cda12b0d-8ade-11eb-9670-04d4c4aa3454",
        "PRICE1": 1260
      },
      {
        "ID_WARE": "5a9afc42-8b1c-11eb-9671-04d4c4aa3454",
        "PRICE1": 0
      },
      {
        "ID_WARE": "bcd19d0d-8d42-11eb-9671-04d4c4aa3454",
        "PRICE1": 12600
      },
      {
        "ID_WARE": "598faa9f-983d-11eb-9674-04d4c4aa3454",
        "PRICE1": 1500
      },
      {
        "ID_WARE": "1c77169e-9b73-11eb-9674-04d4c4aa3454",
        "PRICE1": 1000
      },
      {
        "ID_WARE": "1c77169f-9b73-11eb-9674-04d4c4aa3454",
        "PRICE1": 1500
      }
    ]
  }
}</m:return>
    </m:GETResponse>
  </soap:Body>
</soap:Envelope>' soap from dual)
select /*+ no_merge(x)*/
       j.*
  from t
     , xmltable( xmlnamespaces ('http://www.w3.org/2003/05/soap-envelope' as "soap"
                               ,'http://localhost/NOXT/' as "m")
               , '/soap:Envelope/soap:Body/m:GETResponse'
               passing xmltype(t.soap)
               columns j clob path 'm:return'
       ) x
     , json_table(x.j, '$.object.inf_prices[*]'
       columns( ID_WARE path '$.ID_WARE'
			        , PRICE1 path '$.PRICE1'
			        )
       ) j
;

ID_WARE                                                                          PRICE1
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
12a14479-a2a3-11eb-8c3d-a8a159650ac5                                             0
a3f8c04b-a7f9-11eb-8c3d-a8a159650ac5                                             15000
8615a769-c52b-11eb-8c3d-a8a159650ac5                                             0
5a7ebf47-d8ad-11eb-8c3f-a8a159650ac5                                             1500
4a3a4fb7-1479-11ec-8c49-a8a159650ac5                                             1100
7c07335c-16e6-11ec-8c49-a8a159650ac5                                             1100
969031c3-1a09-11ec-8c4a-a8a159650ac5                                             1200
888893be-1c55-11ec-8c4a-a8a159650ac5                                             1500
45c7aef1-1c64-11ec-8c4a-a8a159650ac5                                             0
58db63cc-1c64-11ec-8c4a-a8a159650ac5                                             0
a97fbd7d-1f66-11ec-8c4a-a8a159650ac5                                             1200
de526b65-42e8-11ec-8c4d-a8a159650ac5                                             1100
65d55968-92b6-11eb-9524-181deaf1436f                                             1300
cda12b0d-8ade-11eb-9670-04d4c4aa3454                                             1260
5a9afc42-8b1c-11eb-9671-04d4c4aa3454                                             0
bcd19d0d-8d42-11eb-9671-04d4c4aa3454                                             12600
598faa9f-983d-11eb-9674-04d4c4aa3454                                             1500
1c77169e-9b73-11eb-9674-04d4c4aa3454                                             1000
1c77169f-9b73-11eb-9674-04d4c4aa3454                                             1500

19 rows selected


SQL> 
24 ноя 21, 16:18    [22400264]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить