How do I select for each row from a list of elements in an XML column?

Question

I have TableC and TableA. I want all the records from TableC whereas only matching records from TableA so I'm using 'left join'. The problem is that TableA has an XML column. The XML in that column has following structure

<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>

I want to get createdBy from XML column for each row where equivalent type is 'ccc'.

Below is my effort

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 

However, I'm getting an error stating

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Can anyone please guide on how to achieve what I'm trying to do here?

P.S Later I would also like to get 'target' from XML column for each row where equivalent user is 'userA'.

Answer 1

(select prop1 from res1) prop1

This is the part of your query that is causing the error. If you want to use this as a subquery, it must return one row for each row of your statement:

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

I know nothing about XML querying, but in order to make this query work, you will need to add an ID to the res1 CTE.

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')

And then change your subquery to be:

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

I realize that my answer only solves the subquery portion of your question, but I hope this helps solve the immediate issue. Someone with more experience querying XML might be able to provide a better overall solution, without the CTE.