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

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

2 ответа

Решение

Спасибо всем за ваши комментарии, но мне удалось найти нужное мне решение, создав объединение, которое работает для этого экземпляра. Хорошая вещь об этом, то, что это будет работать независимо от того, сколько записей бросает нам продавец. В некоторых случаях атрибуты "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)
Источник

Вы должны иметь возможность использовать 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, я обновлю ответ, если подумаю над этим.

Источник
Другие вопросы по тегам