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

Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ The problem is the subquery but without some details we can't really offer much in the way of details about to return the information you want. — Sean Lange, Apr 26 '16 at 15:33

score 2 · Accepted Answer · answered Apr 26 '16 at 15:37

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

@ArthurDaniels The problem is that there can be two object of type 'ccc' which will again return more than 1 result in the subquery. How can select the latest one among the multiple returned rows? — fredzyadi, Apr 26 '16 at 19:02
The latest one issue is a common problem. I posted an answer to that earlier, but I can summarize how to do it. I intend to write an article about how to do it. When you want to select the highest one in the list, you can use the NOT EXISTS clause to select from the table, where there does not exist a higher record. See my answer here for an example: http://stackoverflow.com/questions/36849391/find-the-most-recent-date-in-a-result-set/36850246#36850246 — Arthur D, Apr 26 '16 at 19:44

score 2 · Answer 2 · edited Jun 20 '20 at 09:12

2

If I get this correctly you are creating a CTE, thinking, you need this to get your prop1. And then you do exactly the same joins and filters again...

Wouldn't it be enough to reduce this to:

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'

As Arthur Daniels has pointed out, the problem is the (select prop1 from res1) prop1 which will return more than one element and therefore cannot be called as column within a sub-select...

EDIT: How do shred your XML

Removed....

EDIT 2: I must admit you should really train on how do I explain what I need ...

Might be that you are looking for this:

This will joing TableC and TableA as you did it yourself and then pick the value of "createdBy" where the "type" = "ccc".

The next XQuery first picks the username we found in the first go at "ccc" and finds the fitting "target".

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

edited Jun 20 '20 at 09:12

Community

1
1

answered Apr 26 '16 at 15:41

Shnugo

66,100
9
53
114

The reason I can't reduce it as you have suggested is that I have few other properties that I need to extract from the XML column as well. Those properties requires another cross apply. I'm confused right now :D – fredzyadi Apr 26 '16 at 15:50
@fredzyadi, How is your XML actually looking like? If you want to read the `prop2` too, just add `x.xmlCol.value('(prop2)[1]', 'varchar(500)') prop2` and you'll get both... – Shnugo Apr 26 '16 at 15:54
@fredzyadi, what is the difference between `sub` and `sub2`? If they are "the same" you could replace the `x:sub` with `x:*` to open your nodes with `.nodes('x:main/x:*/x:obj')` – Shnugo Apr 26 '16 at 16:16
They are 2 different sub categories. The name might be different than sub and sub 2. – fredzyadi Apr 26 '16 at 16:17
I have elaborated the example even further so it will remove any ambiguity in you mind. – fredzyadi Apr 26 '16 at 16:29
Shnugo, thanks for going the extra mile here. I learned a bit about how to query xml name spaces today. I don't use XML in my day-to-day. – Arthur D Apr 26 '16 at 19:47
Hi @Shnugo, thank you for your time and effort, however, your answer was not helpful. As Arthur D pointed out I needed to bind an id together with the CTE. That solved the issue. I can upvote your answer if you like but cannot mark it as answer. – fredzyadi Apr 27 '16 at 13:54

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

2 Answers2

EDIT: How do shred your XML

EDIT 2: I must admit you should really train on how do I explain what I need ...