July 6, 2009 at 6:12 pm
I have created a properly formatted itunes RSS XML Feed for podcasting mp3 files, however i wanted to clean up the XML Namespace appearing more than once. I don't think it causes any issues but the file output does not look the same as the example i was given.
Here is a summary of my T-SQL statement:
WITH XMLNAMESPACES ('http://www.itunes.com/dtds/podcast-1.0.dtd' as itunes)
SELECT '2.0' AS '@version',
(SELECT ............
(SELECT..........
FROM sqltablename1 for xml path ('item'), TYPE
) FOR XML PATH('channel'), TYPE
) FOR XML PATH('rss')
As you can see i have 3 embedded sql statements that use 'FOR XML PATH' to produce the XML tag wrappers.
1) 'rss' wrapper tag
2) 'channel' wrapper tag
3) 'item' wrapper tags. This is a repeating record set of SQL table row for the feed.
The problem is in my output. Using the line 'WITH XMLNAMESPACES' outputs the itunes namespace on all 3 tag
elements.
How do i only produce the namespace within the 'rss' tag for the entire document to use?
Refer attachment text file for the XML sample:
Can someone tell me how to avoid repeating the namespaces in each tag?
July 20, 2009 at 2:38 am
This is a known, although not harmful issue with nested FOR XML queries.
You can vote for it to be fixed at Connect:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=265956
Anyway, the XML produced by the query still conforms to the standard, it might be just a little more difficult to read, but the machines won't have any problems with it.
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply