October 18, 2007 at 1:56 pm
I am issuing the following query.
===========================
SELECT A.ID "@ID"
,a.col1 "Col1"
, (SELECT COL2
FROM TABLE_A X
WHERE A.ID=X.ID
ORDER BY X.IDseq
FOR XML PATH('')
) AS "MediaURL"
FROM TABLE_A A
FOR XML PATH('CreateTag')
============================
check the attached results.txt file
October 19, 2007 at 7:45 am
Anybody? I have been searching the web for a solution and can not find anything.
October 19, 2007 at 8:40 am
Just so I had some data to mess with, I modified your query to use something form AdventureWorks:
SELECT A.ProductId "@ID"
,a.Name "Col1"
, (SELECT x.StandardCost
FROM [Production].[ProductCostHistory] X
WHERE A.ProductId=X.[ProductID]
ORDER BY X.StartDate
FOR XML PATH('')
) AS "MediaURL"
FROM [Production].[Product] A
FOR XML PATH('CreateTag')
It came back the same way as yours. So I did this change:
SELECT a.ProductId "@ID"
,a.NAME "Col1"
,x.[StandardCost] "MediaUrl/StandardCost"
FROM [Production].[Product] A
JOIN [Production].[ProductCostHistory] X
ON a.productId = x.productid
FOR XML PATH('CreateTag')
Now it works the way you exepct.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 19, 2007 at 9:17 am
This did not work for me. The output as far as the escpaed signs were corrected but the list of items came out wrong, where as it created an individual record for each list item per id. so if I had items I got two records:
Example with out the tags:
Id =1
media
item 1 /item
/media
/id
Id =1
media
item 2 /item
/media
/id
when I need:
Id =1
media
item 1/item
item 2/item
/media
/id
also my query is doing a self join since there are mulitple rows per id with the differnce being the item
October 19, 2007 at 10:42 am
This may help for all of those trying to help (much appreciated)
create table foo
(
localID int,
itemurl varchar(30),
photoSeq int
)
insert into foo
(localID,itemurl,photoSeq)
Values
(1,'http://mysite.com/1.jpg',1)
insert into foo
(localID,itemurl,photoSeq)
Values
(1,'http://mysite.com/2.jpg',2)
insert into foo
(localID,itemurl,photoSeq)
Values
(1,'http://mysite.com/3.jpg',3)
SELECT DISTINCT LocalID as "@ID"
, (SELECT ItemUrl
FROM foo X
WHERE f.LocalID=x.LocalID
ORDER BY x.photoseq
FOR XML PATH('')
) AS "Media"
FROM foo f
FOR XML PATH('CreateAd'), ROOT('AdMissionFeed')
October 19, 2007 at 10:50 am
Oops.
Got it. Add this to the inner join:
FOR XML PATH (''), type
so it looks like this:
SELECT DISTINCT LocalID as "@ID"
, (SELECT ItemUrl
FROM foo X
WHERE f.LocalID=x.LocalID
ORDER BY x.photoseq
FOR XML PATH(''), type
) AS "Media"
FROM foo f
FOR XML PATH('CreateAd'), ROOT('AdMissionFeed')
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 19, 2007 at 11:30 am
okay I did that and it worked. Using my working example
SELECT DISTINCT F.LocalID as "@ID"
, (SELECT x.ItemUrl
FROM [WEB-NRT-SQLPRD5].RDC_AllMetros.dbo.foo X
WHERE f.LocalID=x.LocalID
ORDER BY x.photoseq
FOR XML PATH(''), type
) AS "Media"
FROM [WEB-NRT-SQLPRD5].RDC_AllMetros.dbo.foo f
FOR XML PATH('CreateAd'), ROOT('AdMissionFeed')
however I can not use the the DISTINCT in the select now,
October 19, 2007 at 11:31 am
Grant when I run your query I get this error
Msg 421, Level 16, State 1, Line 1
The xml data type cannot be selected as DISTINCT because it is not comparable.
October 19, 2007 at 11:40 am
I tested using the original query, not the DISTINCT. Hang on.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 19, 2007 at 12:10 pm
ecreese (10/19/2007)
Grant when I run your query I get this errorMsg 421, Level 16, State 1, Line 1
The xml data type cannot be selected as DISTINCT because it is not comparable.
Sorry, the DISTINCT wasn't part of the original query. The thing is, with the "type" statement added to the derived table, we're returning XML instead of text, which is what you needed. DISTINCT can't be applied to the XML data type, but it works when you've got a string. The original query was entitizing the results, hence the funky characters, in order to create the text.
I'm not sure how to satisfy both requirements. If anything should go, I'd look into doing what is necessary to get rid of the DISTINCT.
Maybe there's a way to use the xml.VALUE method to clean it up. I'll experiment a bit.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 19, 2007 at 12:39 pm
Sorry, I'm stumped. I couldn't do both. I got to learn a bit about the query and value methods of xml data types that I wasn't that aware of, but no good answer has come up.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply