XML Workshop XXI - Generating an ATOM 1.0 Feed with FOR XML EXPLICIT

Question

XML Workshop XXI - Generating an ATOM 1.0 Feed with FOR XML EXPLICIT

jacob sebastian

SSChampion

Points: 11822
More actions
July 16, 2008 at 12:41 am

#381719

Comments posted to this topic are about the item XML Workshop XXI - Generating an ATOM 1.0 Feed with FOR XML EXPLICIT
.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Aleksandr Lutsky Old Hand Points: 326 More actions · Answer 1

[p]All this generation script can be a bit more simple if using temporary tables or variables by type table. This can make more simple process of writing long query for XML explicit.

Instead of this all what You need is to declare table with all necessary fields and made them nullable. After this instead of all UNION's all what You need is to write simple inserts in this table.[/p]

As example for Your step 3 script will be the following:

declare @tmpxml table (

tag int

, parent int null

, feed1 nvarchar(max) null

, feed1xmlns nvarchar(max) null

, feed1idelement nvarchar(max) null

, feed1updelement nvarchar(max) null

, title2 nvarchar(max) null

, title2type nvarchar(max) null

)

insert into @tmpxml (tag, feed1xmlns, feed1idelement, feed1updelement)

select 1, 'http://www.w3.org/2005/Atom', id, CONVERT(nvarchar,updated,127) + 'Z'

from feed

insert into @tmpxml (tag, parent, title2, title2type)

select 2, 1, title, 'html'

from feed

select

tag

, parent

, [feed!1!] = feed1

, [feed!1!xmlns] = feed1xmlns

, [feed!1!id!element] = feed1idelement

, [feed!1!updated!element] = feed1updelement

, [title!2!] = title2

, [title!2!type] = title2type

from @tmpxml

order by tag, parent

for xml explicit

XML result will be the same as in Yous Step 3.

[p]In this small sample it can be confusing, but You don't need to add all nulls in the all UNION sections. And in more longest queries it can help to find errors more fast. Among this, when You decided to add more information to Your result XML, all what You need to do is to add necessary columns to the table declaration, needed inserts in the each place where You want and select with correspondent names in the end of Your code. You don't need to start finding all needed places for NULLs in all Your long query.[/p]

[p]If we will add needed data for the step 4, script will be the following (changes are underlined):[/p]

declare @tmpxml table (

tag int

, parent int null

, feed1 nvarchar(max) null

, feed1xmlns nvarchar(max) null

, feed1idelement nvarchar(max) null

, feed1updelement nvarchar(max) null

, title2 nvarchar(max) null

, title2type nvarchar(max) null

, subtitle3 nvarchar(max) null

, subtitle3type nvarchar(max) null)

insert into @tmpxml (tag, feed1xmlns, feed1idelement, feed1updelement)

select 1, 'http://www.w3.org/2005/Atom', id, CONVERT(nvarchar,updated,127) + 'Z'

from feed

insert into @tmpxml (tag, parent, title2, title2type)

select 2, 1, title, 'html'

from feed

insert into @tmpxml (tag, parent, subtitle3, subtitle3type)

select 3, 1, subtitle, 'html'

from feed

select

tag

, parent

, [feed!1!] = feed1

, [feed!1!xmlns] = feed1xmlns

, [feed!1!id!element] = feed1idelement

, [feed!1!updated!element] = feed1updelement

, [title!2!] = title2

, [title!2!type] = title2type

, [subtitle!3!] = subtitle3

, [subtitle!3!Type] = subtitle3type

from @tmpxml

order by tag, parent

for xml explicit

XML result will be the same as in Yous Step 4.

As You can see, changes of all other parts of You query are minor.

In the same way You can test Your result query by parts, use JOIN's to other tables during inserting data or in the final SELECT, etc.

Laurie Bantin Grasshopper Points: 15 More actions · Answer 2

Have you read this article?

http://msdn.microsoft.com/en-us/library/ms345137.aspx

i think you can write the query in the new syntax much more clearly and easily (if you are using SQL Server 2005)

Stan_Segers SSC-Addicted Points: 483 More actions · Answer 3

Laurie Bantin (7/16/2008)
Have you read this article?
http://msdn.microsoft.com/en-us/library/ms345137.aspx
i think you can write the query in the new syntax much more clearly and easily (if you are using SQL Server 2005)

Exactly my question, why use FOR XML EXPLICIT and not FOR XML PATH?

jacob sebastian SSChampion Points: 11822 More actions · Answer 4

Stan Segers (7/16/2008)
Laurie Bantin (7/16/2008)
Have you read this article?
http://msdn.microsoft.com/en-us/library/ms345137.aspx
i think you can write the query in the new syntax much more clearly and easily (if you are using SQL Server 2005)
Exactly my question, why use FOR XML EXPLICIT and not FOR XML PATH?

I had presented another article earlier in this series which uses FOR XML PATH. But FOR XML PATH is only for SQL Server 2005/2008. It is not supported in SQL Server 2000. FOR XML EXPLICIT is for SQL Server 2000 users.

I had included the following in the introduction. "In the previous session, we have seen how to generate an ATOM 1.0 feed using FOR XML PATH. PATH is a new keyword introduced with SQL Server 2005 and is not available in SQL Server 2000. In this session, we will use FOR XML with EXPLICIT to generate an ATOM 1.0 feed. EXPLICIT is available in SQL Server 2000, 2005 and 2008."

Thanks for the comments.

Cheers!

.

mtassin SSC-Insane Points: 23099 More actions · Answer 5

Thanks!

I had known of FOR XML EXPLICIT after having to write a 30 column 7 layer XML query to generate a sales order for Biztalk to pickup. This is a great summary of the pain I had to go through...

This also clued me to FOR XML PATH... which is just plain coolness for generating XML in 2005 and 2008

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Anipaul SSC-Insane Points: 24681 More actions · Answer 6

Excellent article. It was very informative. One good thing is that the code will run from SQL 2000. Thanks.