July 16, 2015 at 2:30 am
Hi:
I have three tables: item, item_attachments, item_xml. The item_xml table has XML data in an nvarchar column. The item table references multiple attachments and has XML. I'm trying to get each item id, the count of its attachments and a specific value out of the XML. The problem is that the CAST doesn't return a column name and I can't seem to group by it. I've simplified my code as much as possible.
Here is my SQL without the CAST which works:
select i.id, count(att.id)
from item i
inner join item_attachments att on i.id = att.item_id
group by i.id
This returns item ids with a count of the attachments associated with each item id.
I can also get the xml value without counts:
select i.id,
CAST(ixml."xml" as xml).value('(/xml/item/tafeqld/institute)[1]', 'nvarchar(200)')
from item i
inner join item_xml ixml on i.id = ixml.id
This returns an item and the (unique) value in /xml/item/tafeqld/institute. When I look at the output, the XML data column has no column name.
Problem is trying to combine them. Nothing that I've tried works.
select i.id,
CAST(ixml."xml" as xml).value('(/xml/item/tafeqld/institute)[1]', 'nvarchar(200)') as 'ItemXML',
count(att.id)
from item i
inner join item_xml ixml on i.id = ixml.id
inner join item_attachments att on i.id = att.item_id
group by i.id, ItemXml
Obviously, this doesn't work because you can't use aliases in group by statements. But I've also tried reproducing the CAST clause in the group by and that doesn't work either.
Any help would be greatly appreciated!
Thanks,
Carl
August 13, 2015 at 7:36 am
Put the query you have, without the group by, in a derived table and do the group by in the main query.
Not sure about your table structures and relations but something like this should get you started.
select T.id,
T.Institute,
count(T.att_id)
from (
select i.id,
cast(ixml."xml" as xml).value('(/xml/item/tafeqld/institute/text())[1]', 'nvarchar(200)') as Institute,
att.id as att_id
from item i
inner join item_xml ixml on i.id = ixml.id
inner join item_attachments att on i.id = att.item_id
) as T
group by T.id,
T.Institute
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply