Как выбрать для каждой строки из списка элементов в столбце XML?

У меня есть TableC и TableA. Мне нужны все записи из таблицы C, тогда как только соответствующие записи из таблицы A, поэтому я использую «левое соединение». Проблема в том, что TableA имеет столбец XML. XML в этом столбце имеет следующую структуру

<x:main xmlns:x="x-elements">
  <x:rules>
    <x:obj>
        <ruleName>name1</ruleName>
        <createdBy>userA</createdBy>
        <type>bbb</type>
    </x:obj>
    <x:obj>
        <ruleName>name2</ruleName>
        <createdBy>userA</createdBy>
        <type>ccc</type>
    </x:obj>
   </x:rules>
   <x:info>
    <x:obj>
        <target>ftp:1</target>
        <user>userB</user>
    </x:obj>
    <x:obj>
        <target>ftp:3</target>
        <user>userA</user>
    </x:obj>
  </x:info>
</x:main>

Я хочу получить createdBy из столбца XML для каждой строки, где эквивалент type равен «ccc».

Ниже мои усилия

with xmlnamespaces ('x-elements' as x),
res1 as (select x.xmlCol.value('(createdBy)[1]', 'varchar(500)') prop1
from TableC c 
left join TableA a 
cross apply a.xCol.nodes('x:main/x:rules/x:obj') x(xmlCol)
on c.Id = a.Id 
where x.xmlCol.value('(type)[1]', 'varchar(500)') = 'ccc')
select
c.Name,
(select prop1 from res1) prop1
from TableC c 
left join TableA a 
on c.Id = a.Id

Однако я получаю сообщение об ошибке

Подзапрос вернул более 1 значения. Это не разрешено, когда подзапрос следует за =, !=, ‹, ‹= , >, >= или когда подзапрос используется как выражение.

Может ли кто-нибудь указать, как добиться того, что я пытаюсь сделать здесь?

PS Позже я также хотел бы получить «цель» из столбца XML для каждой строки, где эквивалент user - это «userA».


xml sql-server xml-parsing cross-apply
person fredzyadi    schedule 26.04.2016    source источник
comment
Вот отличное место для начала. spaghettidba.com /2015/04/24/ Проблема заключается в подзапросе, но без некоторых подробностей мы не можем предложить много подробностей о том, как вернуть нужную информацию.   -  person Sean Lange    schedule 26.04.2016

Ответы (2)


arrow_upward
2
arrow_downward 

(select prop1 from res1) prop1

Это часть вашего запроса, которая вызывает ошибку. Если вы хотите использовать это как подзапрос, он должен возвращать одну строку для каждой строки вашего оператора:

select
c.Name,
(select prop1 from res1) prop1
from TableC c 
left join TableA a 
on c.Id = a.Id

Я ничего не знаю о XML-запросах, но для того, чтобы этот запрос работал, вам нужно будет добавить идентификатор в CTE res1.

res1 as (select x.xmlCol.value('(prop1)[1]', 'varchar(500)') prop1
,c.Id
from TableC c 
left join TableA a 
cross apply a.xCol.nodes('x:main/x:sub/x:obj') x(xmlCol)
on c.Id = a.Id 
where x.xmlCol.value('(prop2)[1]', 'varchar(500)') = 'ccc')

А затем измените свой подзапрос на:

(select prop1 from res1 where res1.Id = c.Id) prop1

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

person Arthur D    schedule 26.04.2016
comment
@ArthurDaniels Проблема в том, что может быть два объекта типа «ccc», которые снова вернут более 1 результата в подзапросе. Как выбрать последнюю из нескольких возвращаемых строк? - person fredzyadi; 26.04.2016
comment
Последняя проблема является общей проблемой. Я разместил ответ на этот вопрос ранее, но я могу обобщить, как это сделать. Я намерен написать статью о том, как это сделать. Если вы хотите выбрать самую высокую запись в списке, вы можете использовать предложение NOT EXISTS для выбора из таблицы, где не существует более высокой записи. См. мой ответ здесь для примера: найти самую последнюю дату в наборе результатов "> stackoverflow.com/questions/36849391/ - person Arthur D; 26.04.2016

arrow_upward
2
arrow_downward

Если я правильно понимаю, вы создаете CTE, думая, что вам это нужно, чтобы получить свой prop1. И затем вы снова делаете точно такие же объединения и фильтры...

Не будет ли достаточно сократить это до:

with xmlnamespaces ('x-elements' as x)
select x.xmlCol.value('(prop1)[1]', 'varchar(500)') prop1
from TableC c 
left join TableA a 
cross apply a.xCol.nodes('x:main/x:sub/x:obj') x(xmlCol)
on c.Id = a.Id 
where x.xmlCol.value('(prop2)[1]', 'varchar(500)') = 'ccc'

Как указал Артур Дэниелс, проблема заключается в том, что (select prop1 from res1) prop1 возвращает более одного элемента и, следовательно, не может быть вызван как столбец внутри подвыборки...

РЕДАКТИРОВАТЬ: Как уничтожить ваш XML

Удаленный....

РЕДАКТИРОВАТЬ 2: Должен признать, вам действительно следует потренироваться как мне объяснить, что мне нужно...

Возможно, вы ищете это:

Это объединит TableC и TableA, как вы это сделали сами, а затем выберите значение createdBy, где type = ccc.

Следующий XQuery сначала выбирает имя пользователя, которое мы нашли при первом обращении к ccc, и находит подходящую цель.

WITH XMLNAMESPACES('x-elements' AS x)
SELECT c.*
      ,a.*
      ,a.xCol.value('(//x:rules/x:obj[type="ccc"]/createdBy)[1]','varchar(500)') AS CreatedBy
      ,a.xCol.value('let $user:=(//x:rules/x:obj[type="ccc"]/createdBy)[1] return (//x:info/x:obj[user=$user]/target)[1]','varchar(500)') AS Target
FROM TableC AS c 
LEFT JOIN TableA AS a on c.Id = a.Id
person Shnugo    schedule 26.04.2016
comment
Причина, по которой я не могу уменьшить его, как вы предложили, заключается в том, что у меня есть несколько других свойств, которые мне нужно также извлечь из столбца XML. Эти свойства требуют другого перекрестного применения. Я сейчас в замешательстве :D - person fredzyadi; 26.04.2016
comment
@fredzyadi, как на самом деле выглядит ваш XML? Если вы хотите прочитать и prop2, просто добавьте x.xmlCol.value('(prop2)[1]', 'varchar(500)') prop2, и вы получите оба... - person Shnugo; 26.04.2016
comment
@fredzyadi, в чем разница между sub и sub2? Если они одинаковы, вы можете заменить x:sub на x:*, чтобы открыть свои узлы с помощью .nodes('x:main/x:*/x:obj'). - person Shnugo; 26.04.2016
comment
Это 2 разные подкатегории. Имя может отличаться от sub и sub 2. - person fredzyadi; 26.04.2016
comment
Я еще больше развил пример, чтобы он устранил любую двусмысленность в вашем уме. - person fredzyadi; 26.04.2016
comment
Shnugo, спасибо, что сделали все возможное. Сегодня я немного узнал о том, как запрашивать пространства имен xml. Я не использую XML в повседневной жизни. - person Arthur D; 26.04.2016
comment
Привет @Shnugo, спасибо за ваше время и усилия, однако ваш ответ не помог. Как указал Артур Д., мне нужно было связать идентификатор с CTE. Это решило проблему. Я могу проголосовать за ваш ответ, если хотите, но не могу пометить его как ответ. - person fredzyadi; 27.04.2016