SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
Search:  
 
 
        
Home       Members    Calendar    Who's On



XML Workshop XXI - Generating an ATOM 1.0 Feed with FOR XML EXPLICIT Expand / Collapse
Author
Message
Posted Wednesday, July 16, 2008 12:41 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 1:12 PM
Points: 414, Visits: 2,250
Comments posted to this topic are about the item XML Workshop XXI - Generating an ATOM 1.0 Feed with FOR XML EXPLICIT

Jacob Sebastian, SQL Server MVP
http://beyondrelational.com/blogs/jacob/
Post #534910
Posted Wednesday, July 16, 2008 2:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 12, 2009 2:21 AM
Points: 2, Visits: 11

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.



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.


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.


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


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.
Post #534969
Posted Wednesday, July 16, 2008 2:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 3:12 AM
Points: 1, Visits: 23

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)
Post #534981
Posted Wednesday, July 16, 2008 2:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 11, 2009 6:48 AM
Points: 2, Visits: 55
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?
Post #534994
Posted Wednesday, July 16, 2008 4:29 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 1:12 PM
Points: 414, Visits: 2,250
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!


Jacob Sebastian, SQL Server MVP
http://beyondrelational.com/blogs/jacob/
Post #535063
Posted Wednesday, July 16, 2008 7:46 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:59 AM
Points: 1,398, Visits: 6,550
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
Proud member of the Anti-RBAR alliance.

For help with Performance click this link
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

For tips on how to post your problems
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #535199
Posted Wednesday, July 16, 2008 9:10 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 02, 2009 9:26 AM
Points: 3,280, Visits: 962
Excellent article. It was very informative. One good thing is that the code will run from SQL 2000. Thanks.


Post #535312
« Prev Topic | Next Topic »


Permissions Expand / Collapse