Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Из XML в таблицу  [new]
Гость123
Guest
Друзья!

Есть переменная типа Varchar2, в который содержится некий XML.

<SOAP:Envelope xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">
	<SOAP:Header/>
	<SOAP:Body>
		<ns1:_V_SEARCHResponse xmlns:ns1="..." cid="1">
			<V_SEARCH.Response>
				<ET_VDATA>
				    <item>
				        <FNAME>И</FNAME>
				        <LNAME>Иванов</LNAME>
				    </item>
				    <item>
				        <FNAME>П</FNAME>
				        <LNAME>Петров</LNAME>
				    </item>
				</ET_VDATA>
			</V_SEARCH.Response>
		</ns1:_V_SEARCHResponse>
	</SOAP:Body>
</SOAP:Envelope>

Есть табличка T1 с полями fname и lname.

Подскажите, как экспортировать значения из этого XML, из тегов <FNAME>, <LNAME> в соответсвующие поля таблицы T1.
18 авг 09, 10:41    [7549895]     Ответить | Цитировать Сообщить модератору
 Re: Из XML в таблицу  [new]
Oraзработчик
Member

Откуда:
Сообщений: 12
WITH t AS(
SELECT XMLTYPE('<SOAP:Envelope xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">
  <SOAP:Header/>
  <SOAP:Body>
    <ns1:_V_SEARCHResponse xmlns:ns1="..." cid="1">
      <V_SEARCH.Response>
        <ET_VDATA>
            <item>
                <FNAME>И</FNAME>
                <LNAME>Иванов</LNAME>
            </item>
            <item>
                <FNAME>П</FNAME>
                <LNAME>Петров</LNAME>
            </item>
        </ET_VDATA>
      </V_SEARCH.Response>
    </ns1:_V_SEARCHResponse>
  </SOAP:Body>
</SOAP:Envelope>') xml_field FROM dual)
        SELECT 
               "FNAME", 
               "LNAME"
         FROM
          XMLTABLE(
            XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS "SOAP", '...' AS "ns1"),
            'SOAP:Envelope/SOAP:Body/ns1:_V_SEARCHResponse/V_SEARCH.Response/ET_VDATA/item'
            PASSING (SELECT xml_field FROM t)
            COLUMNS
              "FNAME"   VARCHAR2(100) PATH 'FNAME',
              "LNAME"   VARCHAR2(100) PATH 'LNAME'
          );
18 авг 09, 11:05    [7550024]     Ответить | Цитировать Сообщить модератору
 Re: Из XML в таблицу  [new]
Гость123
Guest
Спасибо. Все работает.

Скажите, а если структура вот такая, т.е. есть еще тег детализации адреса

<SOAP:Envelope xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">
	<SOAP:Header/>
	<SOAP:Body>
		<ns1:_V_SEARCHResponse xmlns:ns1="..." cid="1">
	        <FNAME>И</FNAME>
	        <LNAME>Иванов</LNAME>
	        <ADDR_DETAILS>
		    <item>
	        	<ADDR>г. Москва, ул. Воздвиженка</ADDR>
	        	<PHONE>758-23-23</PHONE>
		    </item>
	        <FNAME>П</FNAME>
	        <LNAME>Петров</LNAME>
                <ADDR_DETAILS>
		    <item>
	        	<ADDR>г. Москва, ул. Охотный ряд</ADDR>
	        	<PHONE>169-33-21</PHONE>
	            </item>			
		</ns1:_V_SEARCHResponse>
	</SOAP:Body>
</SOAP:Envelope>

как вывести <FNAME>, <LNAME>, <ADDR>, <PHONE>?
18 авг 09, 15:11    [7551918]     Ответить | Цитировать Сообщить модератору
 Re: Из XML в таблицу  [new]
Гость123
Guest
Так что-то не катит.

        SELECT 
               "FNAME", 
               "LNAME",
               "ADDR",
               "PHONE"
         FROM
          XMLTABLE(
            XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS "SOAP"),
            'SOAP:Envelope/SOAP:Body'
            PASSING (SELECT xml_field FROM t)
            COLUMNS
              "FNAME"   VARCHAR2(100) PATH 'FNAME',
              "LNAME"   VARCHAR2(100) PATH 'LNAME',
              "ADDR" VARCHAR2(100) PATH 'ADDR_DETAILS/item/ADDR',
              "PHONE" VARCHAR2(100) PATH 'ADDR_DETAILS/item/PHONE'
          );

ORA-19279: XQuery dynamic type mismatch:expected singleton sequence-multi item sequence.
18 авг 09, 15:25    [7552036]     Ответить | Цитировать Сообщить модератору
 Re: Из XML в таблицу  [new]
alex-chem
Member

Откуда:
Сообщений: 80
Для начала нужно правильно XML-ку собрать,
</ADDR_DETAILS> 
не хватает
18 авг 09, 19:34    [7553577]     Ответить | Цитировать Сообщить модератору
 Re: Из XML в таблицу  [new]
Гость123
Guest
Да, пропустил </ADDR_DETAILS> здесь в примере. Но вопрос по-прежнему в силе.

<SOAP:Envelope xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">
    <SOAP:Header/>
    <SOAP:Body>
        <ns1:_V_SEARCHResponse xmlns:ns1="..." cid="1">
            <FNAME>И</FNAME>
            <LNAME>Иванов</LNAME>
            <ADDR_DETAILS>
                <item>
                    <ADDR>г. Москва, ул. Воздвиженка</ADDR>
                    <PHONE>758-23-23</PHONE>
                </item>
            </ADDR_DETAILS>
            <FNAME>П</FNAME>
            <LNAME>Петров</LNAME>    
            <ADDR_DETAILS>
                <item>
                    <ADDR>г. Москва, ул. Охотный ряд</ADDR>
                    <PHONE>169-33-21</PHONE>
                </item>            
            </ADDR_DETAILS>
        </ns1:_V_SEARCHResponse>
    </SOAP:Body>
</SOAP:Envelope>
18 авг 09, 21:51    [7553839]     Ответить | Цитировать Сообщить модератору
 Re: Из XML в таблицу  [new]
Гость123
Guest
Попробовал вот так, но что-то тоже на сработало. Та же ошибка. Подскажите как разрешить проблему.

WITH t AS(
SELECT XMLTYPE('
<SOAP:Envelope xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">
    <SOAP:Body>
            <FNAME>И</FNAME>
            <LNAME>Иванов</LNAME>
            <ADDR_DETAILS>
            <item>
                <ADDR>г. Москва, ул. Воздвиженка</ADDR>
                <PHONE>758-23-23</PHONE>
            </item>
            </ADDR_DETAILS>
            <FNAME>П</FNAME>
            <LNAME>Петров</LNAME>
            <ADDR_DETAILS>
            <item>
                <ADDR>г. Москва, ул. Охотный ряд</ADDR>
                <PHONE>169-33-21</PHONE>
            </item>
            </ADDR_DETAILS>
	</SOAP:Body>
</SOAP:Envelope>
') xml_field FROM dual)
SELECT
    a.FNAME,
    a.LNAME,
    b.ADDR
FROM
    XMLTABLE(
    XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS "SOAP"),
    'SOAP:Envelope/SOAP:Body'
    PASSING (SELECT xml_field FROM t)
    COLUMNS
        "FNAME" VARCHAR2(100) PATH 'FNAME',
        "LNAME" VARCHAR2(100) PATH 'LNAME',
        "ADDR_DETAILS" XMLType PATH 'ADDR_DETAILS') a,
    XMLTABLE(
    XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS "SOAP"),
    'ADDR_DETAILS'
    PASSING a."ADDR_DETAILS"
    COLUMNS
        "ADDR" VARCHAR2(100) PATH 'item/ADDR') b;
19 авг 09, 09:01    [7554419]     Ответить | Цитировать Сообщить модератору
 Re: Из XML в таблицу  [new]
Oraзработчик
Member

Откуда:
Сообщений: 12
Гость123,

Гость123
Скажите, а если структура вот такая, т.е. есть еще тег детализации адреса


А структурой XML Вы сами рулите или получаете откуда-то? Уж очень он кривой, на мой вкус, во втором вашем примере получился. Надо бы, чтобы он получался таким:
<SOAP:Envelope xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">
    <SOAP:Header/>
    <SOAP:Body>
        <ns1:_V_SEARCHResponse xmlns:ns1="..." cid="1">
            <item>
              <FNAME>И</FNAME>
              <LNAME>Иванов</LNAME>
              <ADDR_DETAILS>
                      <ADDR>г. Москва, ул. Воздвиженка</ADDR>
                      <PHONE>758-23-23</PHONE>
              </ADDR_DETAILS>
            </item>
            <item>
              <FNAME>П</FNAME>
              <LNAME>Петров</LNAME>    
              <ADDR_DETAILS>
                    <ADDR>г. Москва, ул. Охотный ряд</ADDR>
                    <PHONE>169-33-21</PHONE>
            </ADDR_DETAILS>
          </item>            
        </ns1:_V_SEARCHResponse>
    </SOAP:Body>
</SOAP:Envelope>

Тогда можно будет использовать мой пример.
А по Вашему XML уже xmltable использовать не получится, по-моему. Можно какого-то зверя написать, но правильно исправить xml, чтобы данные были внутри одного элемента.
19 авг 09, 12:09    [7555569]     Ответить | Цитировать Сообщить модератору
 Re: Из XML в таблицу  [new]
Гость123
Guest
Oraзработчик, спасибо за feedback.

К сожалению, данные получаю. Изменить xml, на вид, указанный вами, не является возможным.

Т.е. ваш приговор. XML моего вида можно обработать только с помощью парсинга или пакета dbms_xmldom?
19 авг 09, 13:01    [7555906]     Ответить | Цитировать Сообщить модератору
 Re: Из XML в таблицу  [new]
Oraзработчик
Member

Откуда:
Сообщений: 12
Гость123
Т.е. ваш приговор. XML моего вида можно обработать только с помощью парсинга или пакета dbms_xmldom?


Ну, инструментов для работы с XML масса. Кроме предложенных Вами способов можно воспользоваться таким извращением:

SELECT VALUE(p).GETROOTELEMENT(), extractvalue(VALUE(p),'*/text()') 
  FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE('
<SOAP:Envelope xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">
    <SOAP:Body>
            <FNAME>И</FNAME>
            <LNAME>Иванов</LNAME>
            <ADDR_DETAILS>
            <item>
                <ADDR>г. Москва, ул. Воздвиженка</ADDR>
                <PHONE>758-23-23</PHONE>
            </item>
            </ADDR_DETAILS>
            <FNAME>П</FNAME>
            <LNAME>Петров</LNAME>
            <ADDR_DETAILS>
            <item>
                <ADDR>г. Москва, ул. Охотный ряд</ADDR>
                <PHONE>169-33-21</PHONE>
            </item>
            </ADDR_DETAILS>
  </SOAP:Body>
</SOAP:Envelope>
'),
 '/SOAP:Envelope/SOAP:Body/FNAME|/SOAP:Envelope/SOAP:Body/LNAME|/SOAP:Envelope/SOAP:Body/ADDR_DETAILS/item/ADDR',
 'xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/"'))) p

Далее транспонировать в строки.

PS. Все же попробуйте договориться с поставщиками данного XML.
19 авг 09, 14:26    [7556574]     Ответить | Цитировать Сообщить модератору
 Re: Из XML в таблицу  [new]
Гость123
Guest
Oraзработчик, понял вас. Спасибо за помощь и приведенные примеры.
19 авг 09, 23:48    [7559039]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Из XML в таблицу  [new]
Kuatishe
Member

Откуда: Almaty, best city
Сообщений: 117
Гость123
Да, пропустил </ADDR_DETAILS> здесь в примере. Но вопрос по-прежнему в силе.

<SOAP:Envelope xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">
    <SOAP:Header/>
    <SOAP:Body>
        <ns1:_V_SEARCHResponse xmlns:ns1="..." cid="1">
            <FNAME>И</FNAME>
            <LNAME>Иванов</LNAME>
            <ADDR_DETAILS>
                <item>
                    <ADDR>г. Москва, ул. Воздвиженка</ADDR>
                    <PHONE>758-23-23</PHONE>
                </item>
            </ADDR_DETAILS>
            <FNAME>П</FNAME>
            <LNAME>Петров</LNAME>    
            <ADDR_DETAILS>
                <item>
                    <ADDR>г. Москва, ул. Охотный ряд</ADDR>
                    <PHONE>169-33-21</PHONE>
                </item>            
            </ADDR_DETAILS>
        </ns1:_V_SEARCHResponse>
    </SOAP:Body>
</SOAP:Envelope>

не подскажите, а как экспортировать данные в тэгах в поля другой таблицы?
11 ноя 10, 09:51    [9758773]     Ответить | Цитировать Сообщить модератору
 Re: Из XML в таблицу  [new]
bdsm_sql
Member

Откуда:
Сообщений: 948
использовать обычный запрос к XMLTABLE, а в чем проблема?
11 ноя 10, 10:05    [9758875]     Ответить | Цитировать Сообщить модератору
 Re: Из XML в таблицу  [new]
Kuatishe
Member

Откуда: Almaty, best city
Сообщений: 117
bdsm_sql
использовать обычный запрос к XMLTABLE, а в чем проблема?

проблема в том, что не владею синтаксисом приведенного выше запроса. Т.е. если экспортировать и инсертить тэги в другую таблицу, то куда именно вписывать insert?
А есть какие-либо другие варианты решения? гугле выдал функцию extractvalue, но как понял она применяется если тип поля не varchar2, a xmltype.
11 ноя 10, 12:41    [9760178]     Ответить | Цитировать Сообщить модератору
 Re: Из XML в таблицу  [new]
bdsm_sql
Member

Откуда:
Сообщений: 948
хрень какая-то..
XMLTYPE(varchar2) даст XMLTYPE
если есть запрос на SELECT .. FROM ..
то можно писать INSERT INTO .. SELECT .. FROM ..
11 ноя 10, 13:52    [9760894]     Ответить | Цитировать Сообщить модератору
 Re: Из XML в таблицу  [new]
Kuatishe
Member

Откуда: Almaty, best city
Сообщений: 117
Вот по этому скрипту:

Oraзработчик
1. WITH t AS(
2. SELECT XMLTYPE('<SOAP:Envelope xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">
3.  <SOAP:Header/>
4.  <SOAP:Body>
5.    <ns1:_V_SEARCHResponse xmlns:ns1="..." cid="1">
6.      <V_SEARCH.Response>
7.        <ET_VDATA>
8.            <item>
9.                <FNAME>И</FNAME>
10.                <LNAME>Иванов</LNAME>
11.            </item>
12.           <item>
13.                <FNAME>П</FNAME>
14.                <LNAME>Петров</LNAME>
15.            </item>
16.        </ET_VDATA>
17.      </V_SEARCH.Response>
18.    </ns1:_V_SEARCHResponse>
19.  </SOAP:Body>
20. </SOAP:Envelope>') xml_field FROM dual)
21.        SELECT 
22.               "FNAME", 
23.               "LNAME"
24.         FROM
25.          XMLTABLE(
26.            XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS "SOAP", '...' AS "ns1"),
27.            'SOAP:Envelope/SOAP:Body/ns1:_V_SEARCHResponse/V_SEARCH.Response/ET_VDATA/item'
28.            PASSING (SELECT xml_field FROM t)
29.            COLUMNS
30.              "FNAME"   VARCHAR2(100) PATH 'FNAME',
31.              "LNAME"   VARCHAR2(100) PATH 'LNAME'
32.          );

строки 1-20 это идет перечисление и выборка всех тэгов в поле. В строке 28 показано откуда брать эти теги(т.е. таблица t поле xml_field).
А вот что означает строка 21-25 не понятно, откуда выборка? Да и вообще где экспорт со всеми запросами на вставку?
11 ноя 10, 14:50    [9761418]     Ответить | Цитировать Сообщить модератору
 Re: Из XML в таблицу  [new]
Kuatishe
Member

Откуда: Almaty, best city
Сообщений: 117
Вот хочу тэги из поля pole_xml что в таблице Table1 выбрать в отдельные столбцы, для этого пишу следующее:
Вот формат XML:
автор
<s1>
<s2>
<T1>OperationProperties</T1>
<T2>
<T3>
<Pr>
<Code>202.124</Code>
<Value>Mercedes</Value>
</Pr>
</T3>
</T2>
</s2>
</s1>

Для этого делаю:
автор
WITH t AS(
SELECT XMLTYPE('<s1>
 <s2>
  <T1>OperationProperties</T1> 
<T2>
<T3>
<Pr>
  <Code>202.124</Code> 
  <Value>Mercedes</Value> 
  </Pr>
  </T3>
  </T2>
  </s2>
  </s1>') pole_xml from table1)
select 
"Code",
"Value"

FROM
          XMLTABLE(
            XMLNAMESPACES 's1/s2/t1/t2/t3/pr' 
            PASSING (select pole_xml from table1)
            COLUMNS
              "Code"   VARCHAR2(100) PATH 'Code',
              "Value"   VARCHAR2(100) PATH 'Value'
          )

Выходит сообщение об ошибке XQuery String Literal Expected и указывает на строку XMLNAMESPACES.
12 ноя 10, 07:02    [9764806]     Ответить | Цитировать Сообщить модератору
 Re: Из XML в таблицу  [new]
bdsm_sql
Member

Откуда:
Сообщений: 948
не указывай NAMESPACES вообще. namespaces - это префиксы в имени тэга, перед двоеточием. в этом XML их вообще нет
12 ноя 10, 08:06    [9764861]     Ответить | Цитировать Сообщить модератору
 Re: Из XML в таблицу  [new]
bdsm_sql
Member

Откуда:
Сообщений: 948
XMLTABLE(
            's1/s2/t1/t2/t3/pr' 
            PASSING (select pole_xml from table1)
            COLUMNS
              "Code"   VARCHAR2(100) PATH 'Code',
              "Value"   VARCHAR2(100) PATH 'Value'
          )
вот так..
12 ноя 10, 08:08    [9764868]     Ответить | Цитировать Сообщить модератору
 Re: Из XML в таблицу  [new]
Kuatishe
Member

Откуда: Almaty, best city
Сообщений: 117
bdsm_sql
XMLTABLE(
            's1/s2/t1/t2/t3/pr' 
            PASSING (select pole_xml from table1)
            COLUMNS
              "Code"   VARCHAR2(100) PATH 'Code',
              "Value"   VARCHAR2(100) PATH 'Value'
          )
вот так..

Спасибо. Но теперь появляется сообщение об ошибке на строку PASSING(Inconsistent Data Types : Expected got CHAR). Там ведь запрос на поле из которого выбираем тэги? Он может быть с условием типа where?
12 ноя 10, 08:44    [9764935]     Ответить | Цитировать Сообщить модератору
 Re: Из XML в таблицу  [new]
bdsm_sql
Member

Откуда:
Сообщений: 948
а зачем тебе with? у тебя же данные в таблице table1
тебе надо просто
select 
"Code",
"Value"

FROM
          XMLTABLE(
            's1/s2/t1/t2/t3/pr' 
            PASSING (select pole_xml from table1)
            COLUMNS
              "Code"   VARCHAR2(100) PATH 'Code',
              "Value"   VARCHAR2(100) PATH 'Value'
          )
with обычно используют на этом форуме для работы с тестовыми данными, для примера..
вам он не нужен
12 ноя 10, 09:44    [9765149]     Ответить | Цитировать Сообщить модератору
 Re: Из XML в таблицу  [new]
bdsm_sql
Member

Откуда:
Сообщений: 948
вдобавок. касательно s1/s2/t1/t2/t3/pr
регистр тегов ВАЖЕН
12 ноя 10, 09:44    [9765157]     Ответить | Цитировать Сообщить модератору
 Re: Из XML в таблицу  [new]
bdsm_sql
Member

Откуда:
Сообщений: 948
в-третьих.
у вас неправильный путь. разберитесь со структурой. t1 не в ходит в путь до Code и Value
короче, с тестовыми даными будет так: запустите
WITH t AS(
SELECT XMLTYPE('<s1>
 <s2>
  <T1>OperationProperties</T1> 
<T2>
<T3>
<Pr>
  <Code>202.124</Code> 
  <Value>Mercedes</Value> 
  </Pr>
  </T3>
  </T2>
  </s2>
  </s1>') pole_xml from dual)
select 
"Code",
"Value"

FROM
          XMLTABLE(
            's1/s2/T2/T3/Pr' 
            PASSING (select pole_xml from t)
            COLUMNS
              "Code"   VARCHAR2(100) PATH 'Code',
              "Value"   VARCHAR2(100) PATH 'Value'
          )
вам with не надо, вместо t в PASSING напишите table1
12 ноя 10, 09:46    [9765170]     Ответить | Цитировать Сообщить модератору
 Re: Из XML в таблицу  [new]
Kuatishe
Member

Откуда: Almaty, best city
Сообщений: 117
bdsm_sql
а зачем тебе with? у тебя же данные в таблице table1
тебе надо просто
select 
"Code",
"Value"

FROM
          XMLTABLE(
            's1/s2/t1/t2/t3/pr' 
            PASSING (select pole_xml from table1)
            COLUMNS
              "Code"   VARCHAR2(100) PATH 'Code',
              "Value"   VARCHAR2(100) PATH 'Value'
          )
with обычно используют на этом форуме для работы с тестовыми данными, для примера..
вам он не нужен

Огромное спасибо. Понял, картина вроде прояснилась. Все сделал как вы писали. Но почему-то ошибка осталась ругается на xmlpole в passing :"Inconsistent Data Types : Expected got CHAR". По гуглу это следствие несовместимости типов данных или изменение системных таблиц.
12 ноя 10, 12:50    [9766639]     Ответить | Цитировать Сообщить модератору
 Re: Из XML в таблицу  [new]
bdsm_sql
Member

Откуда:
Сообщений: 948
если поле pole_xml текстового типа, то напиши в PASSING
select XMLTYPE(pole_xml) from table1
и обрати внимание на пару предыдущих сообщений про регистр и путь
12 ноя 10, 12:54    [9766670]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить