Oracle SQL извлекает значение из нескольких элементов

Я провел дни в поисках простого решения следующей проблемы, и мне нужна помощь, пожалуйста. У меня есть таблица Oracle с двумя столбцами: recid (номер счета) в качестве первичного ключа и xmlrecord, в котором хранятся все данные xml. Я пытаюсь экспортировать значения, из которых у нас есть многозначные элементы, для нашего приложения с помощью SQL-запроса. Исключая повреждения данных, всегда будет соответствующий c2 m="1" и c3 m="1", если есть c1 m="1" и так далее. Таблица слишком велика, чтобы нажимать ее несколько раз, чтобы извлечь каждый элемент, поэтому мне нужно вытащить их все из xmlrecord при одном доступе к строке. Я пробовал внутренние соединения (1 = 1) и xmltables, но всегда получаю NULLS в возвращаемых данных или каждое новое совпадение в новой строке. Извлечение значения с верхнего уровня у меня не работает в этом случае из-за структуры xml

Наша структура данных базовой таблицы:

RECID             XMLRECORD
-----------------------------------
0000001           <row><c1>test</c1><c2>test2</c2>....</row>
0000002           <row><c1>test</c1><c2>test2</c2>....</row>

Приведенные выше записи будут работать нормально, поскольку в них нет полей с несколькими значениями. Где я борюсь, так это в том, что данные, хранящиеся в XMLRecord, выглядят следующим образом:

<row>
  <c1>test1</c1>
  <c1 m=1>test1_2</c1>
  <c2>test2</c2>
  <c2 m=1>test2_2</c2>
  <c3>test3</c3>
  <c3 m=1>test3_2</c3>
</row>

Формат вывода, который я хотел бы, приведен ниже:

RECID       Col1     Col2     Col3
-----------------------------------
0000003     test1    test2    test3
0000003     test1_2  test2_2  test3_2
0000004     test1    test2    test3
0000004     test1_2  test2_2  test3_2

sql oracle xml-parsing plsql oracle-xml-db
person Mark    schedule 19.01.2014    source источник
comment
Проблема в том, что ваша XML-структура не работает. Два разных элемента с одинаковыми именами в одном контексте неизбежно вызовут проблемы.   -  person APC    schedule 20.01.2014

Ответы (2)


arrow_upward
1
arrow_downward

Спасибо всем за ваши комментарии, но мне удалось получить нужное мне решение, создав соединение, которое работает для этого экземпляра. Хорошая вещь в этом заключается в том, что это будет работать независимо от того, сколько записей предлагает нам поставщик. В некоторых случаях атрибуты «m» достигают 9 или 10.

Я использовал обычное внутреннее соединение (1=1) и построил последующие соединения на основе динамического идентификатора. Результат ID_NUM для первой строки — «c», для следующей строки — «c2» и так далее.

SELECT 
    t.recid
    ,t2.VALUE1 
    ,t3.VALUE2 
    ,t4.VALUE3 
FROM t
INNER JOIN XMLTABLE('/row/c1'
    PASSING t.xmlrecord
    ID_NUM VARCHAR(4) path 'concat(substring(ancestor-or-self::*/name(.),1,1), @m)',
    VALUE1 VARCHAR(20) path '.') t2
ON (1=1)
INNER JOIN XMLTABLE('/row/c2'
    PASSING t.xmlrecord
    ID_NUM VARCHAR(4) path 'concat(substring(ancestor-or-self::*/name(.),1,1), @m)',
    VALUE2 VARCHAR(20) path '.') t3
ON (t2.ID_NUM=t3.ID_NUM)
INNER JOIN XMLTABLE('/row/c3'
    PASSING t.xmlrecord
    ID_NUM VARCHAR(4) path 'concat(substring(ancestor-or-self::*/name(.),1,1), @m)',
    VALUE3 VARCHAR(20) path '.') t4
ON (t2.ID_NUM=t4.ID_NUM)
person Mark    schedule 28.01.2014
comment
XMLTABLE спешит на помощь! Мне жаль человека, который когда-нибудь придет поддерживать эту базу данных :-). Но иногда руководство слышит XML и думает: «О да, мы должны его использовать!» - person N West; 05.02.2014

arrow_upward
0
arrow_downward

Вы должны иметь возможность использовать EXTRACTVALUE() с запросом XPATH, который выбирает элементы на основе атрибута, например.

SELECT RECID
     , EXTRACTVALUE(XMLRECORD, '/row/c1[@m=''1'']')
     , EXTRACTVALUE(XMLRECORD, '/row/c2[@m=''1'']')
     , EXTRACTVALUE(XMLRECORD, '/row/c3[@m=''1'']')
 FROM T

Затем вы могли бы ОБЪЕДИНИТЬ ВСЕ этот результат с помощью

SELECT RECID
     , EXTRACTVALUE(XMLRECORD, '/row/c1[not(@m)]')
     , EXTRACTVALUE(XMLRECORD, '/row/c2[not(@m)]')
     , EXTRACTVALUE(XMLRECORD, '/row/c3[not(@m)]')
 FROM T

Вы можете продолжить UNIONS для количества возможных строк с несколькими атрибутами.

Я не думаю, что это будет легко сделать за одно полное сканирование таблицы, потому что вы пытаетесь создать несколько строк для каждой выбранной вами отдельной строки.

Это отличный пример того, почему хранение XML в реляционной базе данных — довольно плохая идея.

Я пытаюсь придумать способ сделать это с помощью XMLTABLE, я обновлю ответ, если придумаю способ.

person N West    schedule 23.01.2014