http://beyondrelational.com/modules/2/blogs/28/posts/10279/xquery-labs-a-collection-of-xquery-sample-scripts.aspx

link

XML Workshop 26 – SELECT * FROM XML

Introduction

One of the responsibilities I have at work is the performance tuning of the databases. Most of the times, I need to query tables that I am not familiar with. Without any clue about the columns a table has, I often do the following sequence of activities.

SELECT TOP 10 * FROM thatTable
Look at the data to locate the columns I need to process
Once the columns are located rewrite the query as:
SELECT Colx, Coly from thatTable WHERE Colz = SomeValue
I found "SELECT *" quite handy when dealing with tables that I am not familiar with. SQL Server does not provide such an easy interface for querying XML documents. Looking at the large number of XQuery questions on the online forums, I feel it would have been very easy if we were able to do the following:

SELECT * FROM XML document
Look at the elements, attributes and their values
Locate the elements and/or attributes that we are interested
Rewrite the query to select the desired elements and attributes:
SELECT value-of element elx, element ely, attribute atx FROM XML Document
It could have been very easy if there is a way to blindly query an XML document (such as a SELECT *) and a way to uniquely identify the element or attribute that we are interested in and finally tell SQL Server to return us those values.

Keeping this in mind, sometime back I wrote a TSQL function which can be used for querying XML variables or columns. You can find the complete source code listing of the function in this post.

Using the XMLTable() Function

Using the XMLTable() function is quite easy. You can pass an XML document into the function and it will return a tabular representation of the XML data. Let us take the example of an RSS feed and see how we can use the XmlTable() function to read information from it.

The following XML document is extracted from the RSS feed generated by my twitter account. It contains two of my recent tweets (I have modified the RSS feed and created a simpler version for the sake of this demonstration)




http://twitter.com/jacobsebastian
Twitter updates from Jacob Sebastian
en-us
40



We have completed the evaluation of TSQL Challenge 23. http://bit.ly/dmQ63G

Fri, 18 Jun 2010 14:41:12 +0000




This post intends to help you get started with OPENXML(). http://bit.ly/9gThU7

Thu, 17 Jun 2010 12:35:34 +0000



This simplest way to use the XMLTable() function is as follows:

-- In case you want to process an XML Variable
DECLARE @x XML
SELECT @x = '-- XML HERE -- '
SELECT * FROM XMLTable(@x)

-- In case you want to process an XML Column
SELECT * FROM YourTable
CROSS APPLY XMLTable(XMLColumn)
Let us try to play a little with the XMLTable() function and see how it helps to read information from XML documents easily.

Reading all “title” elements from the RSS Feed

The following example retrieves all the “title” elements from the RSS feed.

DECLARE @x XML
SELECT @x = '



http://twitter.com/jacobsebastian
Twitter updates from Jacob Sebastian
en-us
40



We have completed the evaluation of TSQL Challenge 23.
http://bit.ly/dmQ63G

Fri, 18 Jun 2010 14:41:12 +0000




This post intends to help you get started with OPENXML().
http://bit.ly/9gThU7

Thu, 17 Jun 2010 12:35:34 +0000


'

SELECT NodeName, ParentName, Value FROM XMLTable(@x)
WHERE NodeName = 'Title'
/*
NodeName ParentName Value
-------- ---------- --------------------------------------------
title channel Twitter / jacobsebastian
title item XQuery Lab 57 – Getting Started with OPENXML
title item Announcing winners of TSQL Challenge 23
*/
Note that it retrieves the title from the channel as well as item elements. The following example shows how to retrieve the title of item nodes only.

DECLARE @x XML
SELECT @x = '



http://twitter.com/jacobsebastian
Twitter updates from Jacob Sebastian
en-us
40



We have completed the evaluation of TSQL Challenge 23.
http://bit.ly/dmQ63G

Fri, 18 Jun 2010 14:41:12 +0000




This post intends to help you get started with OPENXML().
http://bit.ly/9gThU7

Thu, 17 Jun 2010 12:35:34 +0000


'

SELECT NodeName, ParentName, Value FROM XMLTable(@x)
WHERE NodeName = 'Title'
AND ParentName = 'item'
/*
NodeName ParentName Value
-------- ---------- --------------------------------------------
title item XQuery Lab 57 – Getting Started with OPENXML
title item Announcing winners of TSQL Challenge 23
*/
Auto-Generating the correct XPath expressions

People often send me emails that reads like “I have the following XML document and when I try to read values from xxx node I am getting a NULL”. Almost always I have found that the problem was an incorrect XPath expression. Many people find it really confusing to write the correct XPath expression pointing to a given element or attribute within an XML document.

The XMLTable() function can be used to generate the required XPath expressions for you. The following example demonstrates this.

/*
Retrieve the XPath expression and value of the Item elements
*/
SELECT XPath, Value FROM XMLTable(@x)
WHERE NodeName = 'Title'
AND ParentName = 'item'
/*
XPath Value
---------------------------------- --------------------------------------------
rss[1]/channel[1]/item[2]/title[1] XQuery Lab 57 – Getting Started with OPENXML
rss[1]/channel[1]/item[1]/title[1] Announcing winners of TSQL Challenge 23
*/

/*
Locate, copy and paste the XPath expression into your XQuery
*/

SELECT @x.value('rss[1]/channel[1]/item[1]/title[1]','VARCHAR(100)') AS Title
/*
Title
-------------------------------------------------------------------------------
Announcing winners of TSQL Challenge 23
*/
Reading information from all the item elements

The following example shows how to read all the information from all the item elements in the XML document we examined earlier.

SELECT * FROM XMLTable(@x)
WHERE ParentName = 'Item'
ORDER BY ParentPosition
/*
ParentPosition NodeName Value
-------------- ----------- ---------------------------------------
1 description We have completed the evaluation of T..
1 pubDate Fri, 18 Jun 2010 14:41:12 +0000
1 title Announcing winners of TSQL Challenge ..
2 description This post intends to help you get sta..
2 pubDate Thu, 17 Jun 2010 12:35:34 +0000
2 title XQuery Lab 57 – Getting Started with ..
*/
Our XML document had two item elements. The above query returns values from all the child elements of the two item nodes we had. If you want to read a specific element you can filter it by using the ParentPosition column.

SELECT * FROM XMLTable(@x)
WHERE ParentName = 'Item'
AND ParentPosition = 2
/*
ParentPosition NodeName Value
-------------- ----------- ---------------------------------------
2 description This post intends to help you get sta..
2 pubDate Thu, 17 Jun 2010 12:35:34 +0000
2 title XQuery Lab 57 – Getting Started with ..
*/
Viewing the XML Structure and values

If you want to quickly view the XML structure of the document, you can look at the FullPath and TreeView columns. Here is an example:

SELECT FullPath, TreeView, Value FROM XMLTable(@x)
order by id
/*
FullPath TreeView Value
---------------------------- ------------------ -----------------------------
rss rss NULL
rss/channel |-channel NULL
rss/channel/description |-description Twitter updates from Jacob ..
rss/channel/item |-item NULL
rss/channel/item/description |-description We have completed the evalu..
rss/channel/item/pubDate |-pubDate Fri, 18 Jun 2010 14:41:12 +..
rss/channel/item/title |-title Announcing winners of TSQL ..
rss/channel/item |-item NULL
rss/channel/item/description |-description This post intends to help y..
rss/channel/item/pubDate |-pubDate Thu, 17 Jun 2010 12:35:34 +..
rss/channel/item/title |-title XQuery Lab 57 – Getting Sta..
rss/channel/language |-language en-us
rss/channel/link |-link http://twitter.com/jacobseb..
rss/channel/title |-title Twitter / jacobsebastian
rss/channel/ttl |-ttl 40
*/
Output Reference

The XMLTable() function returns a number of columns that you can use in different ways to solve some of your XQuery requirements. The following is a complete listing of all the columns returned by this function.

ID: Row ID, a unique sequence number
ParentName: Immediate parent name of the current element or attribute
ParentPosition: Position of the parent element within its parent node
Depth: Depth of the current node in the XML document
NodeName: Name of the current element or attribute
Position: Position of the current element in its parent. Always 1 for attributes
NodeType: Type of current member: "element" or "attribute"
FullPath: Full path to the current element/attribute
XPath: XPath expression pointing to the current element/attribute
TreeView: A tree representation indicating the position of the current element or attribute
Value: Text value of the current element or attribute
XmlData: The XML data contained in the current element or attribute
Click here to download the source code of XmlTable() function.

Conclusions

This post demonstrates an easy way to quickly query XML documents. If you find it very hard to deal with XQuery, you might find this function very helpful. XQuery may be a better choice on production environments, for performance reasons.

XML Workshop 25 - Inserting elements and attributes to an XML document

Introduction

In the previous session we examined the "delete" command supported by the "modify()" method of XML data type. In this session we will learn how to insert an element or attribute into an XML document using the XML data type method: modify().

Sample XML Document

Here is the sample XML document that we will use for the examples in this session.

Example 1

Jacob
Smith

Inserting a new element as the first child of a node

The following example inserts a new "Employee" element with value "Steve" as as the first child element of the root node.

Example 2
DECLARE @x XML
SELECT @x = '

Jacob
Smith
'

SET @x.modify('
insert element Employee {"Steve"}
as first
into (Employees)[1]
')

SELECT @x
/*

Steve
Jacob
Smith

*/
The above example created a new XML element using a fixed value specified by a string literal. The next example shows how to create an XML element with value specified in a variable.

Example 3
DECLARE @x xml
SET @x = '

Jacob
Smith
'

DECLARE @name VARCHAR(20)
SELECT @name = 'Steve'

SET @x.modify('
insert element Employee {sql:variable("@name")}
as first
into (Employees)[1]
')

SELECT @x
/*

Steve
Jacob
Smith

*/
Both the examples given above, used the 'insert element' command to create a new element. Another way of achieving the same result is by supplying the whole XML fragment to be inserted as a string.

Example 4
DECLARE @x XML
SELECT @x = '

Jacob
Smith
'

SET @x.modify('
insert Steve
as first
into (Employees)[1]
')

SELECT @x
/*

Steve
Jacob
Smith

*/
If the text value of the element is stored in a variable, the following code will help.

Example 5
DECLARE @x XML
SELECT @x = '

Jacob
Smith
'

DECLARE @name VARCHAR(20)
SELECT @name = 'Steve'

SET @x.modify('
insert {sql:variable("@name")}
as first
into (Employees)[1]
')

SELECT @x
/*

Steve
Jacob
Smith

*/
It is possible to insert the attribute values along with the elements declarations.

Example 6
DECLARE @x XML
SELECT @x = '

Jacob
Smith
'

SET @x.modify('
insert Steve
as first
into (Employees)[1]
')

SELECT @x
/*

Steve
Jacob
Smith

*/
Even if both the text value of the element as well as the value of the attribute are stored in variables, you still dont have anything to worry. The following code shows how to handle it.

Example 7
DECLARE @x XML
SELECT @x = '

Jacob
Smith
'

DECLARE @name VARCHAR(20), @team VARCHAR(20)
SELECT @name = 'Steve', @team = 'SQL Server'

SET @x.modify('
insert

{sql:variable("@name")}

as first
into (Employees)[1]
')

SELECT @x
/*

Steve
Jacob
Smith

*/
Not always you would want to insert the new element as the first child. You can insert the new element as the last child by specifying the "as last" command instead of "as first".

Example 8
DECLARE @x XML
SELECT @x = '

Jacob
Smith
'

SET @x.modify('
insert Steve
as last
into (Employees)[1]
')

SELECT @x
/*

Jacob
Smith
Steve

*/
If you do not include "as first" and "as last" in your XQuery expression, SQL Server will assume "as last". The following code produces the same output as the above example.

Example 9
DECLARE @x XML
SELECT @x = '

Jacob
Smith
'

SET @x.modify('
insert Steve
into (Employees)[1]
')

SELECT @x
/*

Jacob
Smith
Steve

*/
We saw how to insert an element as first or last within a parent node. It is also possible to insert the child element at a specified position. the following example inserts an element as the second child, using the "insert after" command.

Example 10
DECLARE @x XML
SELECT @x = '

Jacob
Smith
'

SET @x.modify('
insert Steve
after (Employees/Employee[1])[1]
')

SELECT @x
/*

Jacob
Steve
Smith

*/
You could also use "insert before" to insert the new element above a specified child. The following example shows how to insert a new element as the second last child of a parent node.

Example 11
DECLARE @x XML
SELECT @x = '

Jacob
Steve
Smith

'

SET @x.modify('
insert Bob
before (Employees/Employee[position()=last()])[1]
')

SELECT @x
/*

Jacob
Steve
Bob
Smith

*/
The position can also be specified by a variable. The following code shows how to insert an element at a position specified by a variable.

Example 12
DECLARE @x XML
SELECT @x = '

Jacob
Steve
Smith

'

DECLARE @pos INT
SELECT @pos = 3 -- insert as the third child

SET @x.modify('
insert Bob
before (Employees/Employee[position()=sql:variable("@pos")])[1]
')

SELECT @x
/*

Jacob
Steve
Bob
Smith

*/
The following example shows how to insert a new element before "steve".

Example 13
DECLARE @x XML
SELECT @x = '

Jacob
Steve
Smith

'

SET @x.modify('
insert Bob
before (Employees/Employee[. = "Steve"])[1]
')

SELECT @x
/*

Jacob
Bob
Steve
Smith

*/
The following example demonstrates how to insert an element right after another element having a text value specified by a variable.

Example 14
DECLARE @x XML
SELECT @x = '

Jacob
Steve
Smith

'

DECLARE @name VARCHAR(20)
SELECT @name = 'Steve' -- insert after steve

SET @x.modify('
insert Bob
after (Employees/Employee[. = sql:variable("@name")])[1]
')

SELECT @x
/*

Jacob
Steve
Bob
Smith

*/
The examples we examined so far inserted relatively simple elements to the XML document. You could even use the "insert" command to add more complex XML fragments having child elements and or attributes.

Example 15
DECLARE @x XML
SELECT @x = '

Jacob
Smith
'

SET @x.modify('
insert

Steve
Mike

as first
into (Employees)[1]
')

SELECT @x
/*


Steve
Mike

Jacob
Smith

*/
It is also possible to insert more than one element in a single operation. The "insert" command can accept a set (comma separated list) of XML elements and insert them at the specified position. Here is an example.

Example 16
DECLARE @x XML
SELECT @x = '

Jacob
Smith
'

SET @x.modify('
insert (
Steve,
Mike
)
into (/Employees)[1]
')

SELECT @x
/*

Jacob
Smith
Steve
Mike

*/
In the previous examples, we saw how to insert new XML elements into an XML document using string literals. The next example shows how to creae a new XML element with the results of an XQuery expression. The following example creates a new XML element named "SQLGuys" and adds all the "SQL Server" people into the group.

Example 17
DECLARE @x XML
SELECT @x = '

Jacob
Smith
Steve
Mike

'

SET @x.modify('
insert {
(/Employees/Employee)
}

into (/Employees)[1]
')

SELECT @x
/*

Jacob
Smith
Steve
Mike

Jacob
Smith
Steve
Mike


*/
Inserting Attributes

The syntax for adding attributes to an XML node is slightly different from that of elements. While working with attributes, remember that the position of an attribute is not significant in XML. An XML element can not have more than one attribute having the same name. Let us see a few examples that deal with inserting attributes into XML nodes.

The following example adds an attribute named "Team" to the XML node representing employee "Jacob".

Example 18
DECLARE @x XML
SELECT @x = '

Steve
Jacob
Smith

'

SET @x.modify('
insert attribute Team {"SQL Server"}
as first
into (Employees/Employee[. = "Jacob"])[1]
')

SELECT @x
/*

Steve
Jacob
Smith

*/
The value of the attribute can be specified by a variable as given in the following example.

Example 19
DECLARE @x XML
SELECT @x = '

Steve
Jacob
Smith

'

DECLARE @team VARCHAR(20)
SELECT @team = 'SQL Server'
SET @x.modify('
insert attribute Team {sql:variable("@team")}
as first
into (Employees/Employee[. = "Jacob"])[1]
')

SELECT @x
/*

Steve
Jacob
Smith

*/
It is also possible to create a new attribute with the value returned by an XQuery expression. The following example creates a new attribute named "Friend" in the element that represents the employee "Jacob". Interestingly the name of Jacob's friend is "Steve".

Example 20
DECLARE @x XML
SELECT @x = '

Steve
Jacob
Smith

'

SET @x.modify('
insert attribute Friend {data(/Employees/Employee[1])}
as first
into (Employees/Employee[. = "Jacob"])[1]
')

SELECT @x
/*

Steve
Jacob
Smith

*/
Well, the attribute value can also be a list of values returned by an XQuery expression. Jacob can have more friends, can't he?

Example 21
DECLARE @x XML
SELECT @x = '

Steve
Jacob
Smith

'

SET @x.modify('
insert attribute Friends {data(/Employees/Employee[. != "Jacob"])}
as first
into (Employees/Employee[. = "Jacob"])[1]
')

SELECT @x
/*

Steve
Jacob
Smith

*/
Multiple attributes can be created with a single query, by specifying a set containing the required number of attributes.

Example 22
DECLARE @x XML
SELECT @x = '

Steve
Jacob
Smith

'

SET @x.modify('
insert (
attribute Team {"SQL"},
attribute Category {"MVP"}
)
as first
into (Employees/Employee[. = "Jacob"])[1]
')

SELECT @x
/*

Steve
Jacob
Smith

*/
The following example shows how to insert new attributes into XML documents stored in an XML colum. What is interesting here is that the values of the attributes are taken from another table by doing a relational join. The code below uses the "sql:column()" function to create attributes with the value stored in a column.

Example 23
DECLARE @team TABLE (
EmpID INT,
Team VARCHAR(20)
)
DECLARE @emp TABLE (
EmpID INT,
Data XML
)

INSERT INTO @emp (EmpID, Data)
SELECT 1, 'Jacob'
INSERT INTO @emp (EmpID, Data)
SELECT 2, 'Steve'
/*
EmpID Data
----------- ---------------------------
1 Jacob
2 Steve
*/

INSERT INTO @team (EmpID, Team) SELECT 1, 'SQL Server'
INSERT INTO @team (EmpID, Team) SELECT 2, 'SQL Azure'
/*
EmpID Team
----------- --------------------
1 SQL Server
2 SQL Azure
*/

UPDATE e
SET Data.modify('
insert attribute Team {sql:column("Team")}
into (/Employee)[1]
')
FROM @emp e
INNER JOIN @team t ON e.EmpID = t.EmpID

SELECT * FROM @emp
/*
EmpID Data
----------- ---------------------------------------------
1 Jacob
2 Steve
*/
Note that attribute names should be unique within an element. If the attribute you are trying to add already exists, you will get an error which says "XML well-formedness check: Duplicate attribute 'name'. Rewrite your XQuery so it returns well-formed XML."

However, it is possible to check for the existence of an attribute and perform a conditional insert. The following code inserts a "Team" attribute to the employee element specified in a variable. The insert operation takes place only if the employee element does not have a "Team" attribute.

Example 24
DECLARE @x XML
SELECT @x = '

Jacob
Steve
Smith
'

DECLARE @name VARCHAR(20), @team VARCHAR(20)
SELECT @name = 'Steve', @team = 'SQL Server'

SET @x.modify('
insert
if (/Employees/Employee[. = sql:variable("@name")]/@Team)
then
()
else
attribute Team {sql:variable("@team")}
as first into (Employees/Employee[. = sql:variable("@name")])[1]
')

SELECT @x
/*

Jacob
Steve
Smith

*/
More fun!

The following example inserts a comment node.

Example 25
DECLARE @x XML
SELECT @x = '

Jacob
Smith
'

SET @x.modify('
insert
as first into (Employees)[1]
')

SELECT @x
/*


Jacob
Smith

*/
The following example adds an XSLT processing instruction to an XML document.

Example 26
DECLARE @x XML
SELECT @x = '

Jacob
Smith
'

SET @x.modify('
insert

as first into (Employees)[1]
')

SELECT @x
/*


Jacob
Smith

*/
The following example shows how to insert a text node into an XML element.

Example 27
DECLARE @x XML
SELECT @x = '

Jacob
Smith
'

SET @x.modify('
insert text {"Best Employees of 2009"}
as first into (Employees)[1]
')

SELECT @x
/*

Best Employees of 2009
Jacob
Smith

*/
Inserting XML data type values

SQL Server 2005 does not allow XML data type variables in the "insert" operation using modify() method. SQL Server 2008 enhanced the modify() method to support XML data type values in the "insert" operation. The following code (runs only on SQL Server 2008 or above) inserts an XML variable into an XML document.

Example 28
DECLARE @x XML
SELECT @x = '

Jacob
'

DECLARE @emp XML
SELECT @emp = 'Steve'

SET @x.modify('
insert sql:variable("@emp")
into (Employees)[1]
')

SELECT @x
/*

Jacob
Steve

*/
Conclusions

In this article, I tried to cover almost all scenarios of "insert" operations that I could quickly think of. If you have some scenarios that are not listed here, do let me know and I will try to add them to the list

XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

Introduction

Most of the articles I presented in the XML Workshop were either generating XML documents or querying XML documents. An area that I have not touched in detail in this series of articles is modifying XML documents. I am dedicating this and the next few sessions to discuss various scenarios of modifying XML documents.

Let us start with deleting elements and attributes from XML documents. We will then discuss INSERT operation and will see how to modify elements and attributes. We will also see how to re-structure XML documents (formatting from one structure to another). The syntax to perform modifications on XML elements and attributes are very close, but it is found to be very confusing to many people. To make this less confusing, I will cover elements and attributes separately.

I have presented an example of deleting an attribute from an XML document in the XQuery Tutorial series that I wrote in my blog. You can find the post here. My blog post shows a basic example of deleting an attribute from an XML document. In this session, we will see close to 2 dozen examples demonstrating different scenarios.

Sample XML Document

Here is the sample XML document that we will use for the examples in this session.

Example 1





Deleting an attribute of an element at a given position

The following example deletes the "Team" attribute from the second "Employee" element

Example 2
DECLARE @x XML
SELECT @x = '




'

SET @x.modify('
delete (Employees/Employee/@Team)[2]
')

SELECT @x
/*





*/
The following example shows another way of writing the same query. The example below uses the position() function.

Example 3
DECLARE @x XML
SELECT @x = '




'

SET @x.modify('
delete (Employees/Employee[position()=2]/@Team)
')

SELECT @x
/*





*/
And here is a third option.

Example 4
DECLARE @x XML
SELECT @x = '




'

SET @x.modify('
delete (Employees/Employee[2]/@Team)
')

SELECT @x
/*





*/
The following example deletes the "Team" attribute from the "last" employee element.

Example 5
DECLARE @x XML
SELECT @x = '




'

SET @x.modify('
delete (Employees/Employee[position()=last()]/@Team)
')

SELECT @x
/*





*/

Deleting an attribute from all the elements

The following example deletes the "Team" attribute from all the "Employee" elements.

Example 6
DECLARE @x XML
SELECT @x = '




'

SET @x.modify('
delete (Employees/Employee/@Team)
')
SELECT @x
/*





*/
Deleting attributes containing specific values

The following example deletes the "Team" attribute from all elements where the value of the attribute is "SQL Server".

Example 7
DECLARE @x XML
SELECT @x = '




'

SET @x.modify('
delete (Employees/Employee/@Team[.="SQL Server"])
')
SELECT @x
/*





*/
The following example uses a 'not equal to' condition. It deletes the "Team" attribute from all elements where the value is not "SQL Server".

Example 8
DECLARE @x XML
SELECT @x = '




'

SET @x.modify('
delete (Employees/Employee/@Team[.!="SQL Server"])
')
SELECT @x
/*





*/
The next example uses an "or" operator to evaluate multiple comparison operations. It deletes the "Team" attribute from all elements where the value of the "Team" attribute is "SQL Server" or "ASP.NET"

Example 9
DECLARE @x XML
SELECT @x = '




'

SET @x.modify('
delete (Employees/Employee/@Team[.="SQL Server" or .="ASP.NET"])
')
SELECT @x
/*





*/
The following example deletes the "city" attribute from all "Employee" elements where the "Team" is "SQL Server".

Example 10
DECLARE @x XML
SELECT @x = '




'

SET @x.modify('
delete (Employees/Employee[@Team="SQL Server"]/@city)
')
SELECT @x
/*





*/
Deleting all attributes

The following examples demonstrate how to delete all the attributes of one or more elements. The first example given below deletes all the attributes of all employee elements.

Example 11
DECLARE @x XML
SELECT @x = '




'

SET @x.modify('
delete (Employees/Employee/@*)
')
SELECT @x
/*





*/
The following example deletes all the attributes of elements where the "Team" is "SQL Server"

Example 12
DECLARE @x XML
SELECT @x = '




'

SET @x.modify('
delete (Employees/Employee[@Team="SQL Server"]/@*)
')
SELECT @x
/*





*/
Deleting attributes by position

The following example deletes the first attribute from all "employee" elements. Note that the order of attributes is not significant in XML. The code below deletes the first attribute by position. Since the deletion is performed using the physical order of the attributes, the first and last elements looses the "name" attribute and the second element looses the "city" attribute.

Example 13
DECLARE @x XML
SELECT @x = '




'

SET @x.modify('
delete (Employees/Employee/@*[position()=1])
')

SELECT @x
/*





*/
The following example deletes the last attribute from all the "Employee" elements.

Example 14
DECLARE @x XML
SELECT @x = '




'

SET @x.modify('
delete (Employees/Employee/@*[position()=last()])
')

SELECT @x
/*





*/
Look at the following example. The second "Employee" element has only one attribute. If we run the same delete statement as the above, the first and last elements will loose the 3rd attribute and the second element will loose the first attribute (because it is the only attribute that the element has)

Example 15
DECLARE @x XML
SELECT @x = '




'

SET @x.modify('
delete (Employees/Employee/@*[position()=last()])
')

SELECT @x
/*





*/
The following example deletes the last attribute from all "Employee" elements of "SQL Server" Team.

Example 16
DECLARE @x XML
SELECT @x = '




'

SET @x.modify('
delete (Employees/Employee[@Team="SQL Server"]/@*[position()=last()])
')

SELECT @x
/*





*/
The following example deletes the last attribute from the last element from "SQL Server" Team (second element in the example)

Example 17
DECLARE @x XML
SELECT @x = '




'

SET @x.modify('
delete (Employees/Employee[@Team="SQL Server"][position()=last()]/@*[position()=last()])
')

SELECT @x
/*





*/
The following example deletes the first attribute from all "Employee" elements if the name of the attribute is "name".

Example 18
DECLARE @x XML
SELECT @x = '




'

SET @x.modify('
delete (Employees/Employee/@*[position()=1 and local-name()="name"])
')

SELECT @x
/*





*/
Here is another way of writing the same code.

Example 19
DECLARE @x XML
SELECT @x = '




'

SET @x.modify('
delete (Employees/Employee/@*[position()=1][local-name()="name"])
')

SELECT @x
/*





*/
Conclusions

In this article, I tried to cover almost all scenarios of deleting attributes, that I could quickly think of. If you have some scenarios that are not listed here, do let me know and I will try to add them to the list.

All the examples we saw in this session use the "modify()" method of XML data type. Another way of doing this is by using a FLOWR operation. I will explain it in another article.

XML Workshop 23 - A TSQL ATOM Library

http://www.sqlservercentral.com/articles/XML/65870/
Printed 2012/02/29 01:28AM
XML Workshop XXIII - A TSQL ATOM Library

By Jacob Sebastian, 2009/02/12

Introduction

In the last few sessions of XML Workshop we had been looking at ways of generating RSS/ATOM Feeds. You can find the previous sessions here. We have seen how to generate RSS and ATOM feeds in SQL Server 2005 as well as 2000. In the previous sessions, we have seen how to generate RSS and ATOM feeds using FOR XML PATH as well as FOR XML EXPLICIT. If you are working with SQL Server 2005 (and above), you can take advantage of FOR XML PATH and if you are still in SQL Server 2000, you can use FOR XML EXPLICIT.

In part XIX of XML Workshop (Generating an ATOM 1.0 Feed) we generated an ATOM 1.0 feed using FOR XML PATH. You need to be very careful when writing this code, because, if the XML does not follow the correct structure and if the values are not in the correct format, the XML document will not be recognized as a valid feed. To make this task easier, in this session, we will create a function that generates an ATOM 1.0 feed from a given feed and entry information. We will create a function that accepts two XML parameters (feed and entry) and generate the required feed structure and returns an XML document.

In XML Workshop XXII, we saw a function that accepts two XML parameters and generates a valid RSS 2.0 feed. In this session we will write the ATOM version of the above function. We will write a function that accepts two XML parameters containing feed and entry information and generates a valid ATOM 1.0 feed. We will be able to call the function as in the example given below.

-- declare the variables
DECLARE @fd XML, @ent XML

-- create an XML document with Feed information
SELECT @fd = (
SELECT column_list FROM your_table
FOR XML PATH(''), ROOT('Feed')
)

-- create an XML document with Entry information
SELECT @ent = (
SELECT column_list FROM your_table
FOR XML PATH ('Entry'), ROOT('Entries')
)

-- generate the feed
SELECT dbo.GenerateAtom10( @fd, @ent )
Function that generates an ATOM 1.0 feed

Let us look at the function that generates an ATOM 1.0 Feed. The code is pretty much the same as what we developed in the previous sessions. The only difference is that, in the previous examples we read the feed information from tables, whereas in this function, we will read information from XML parameters.

CREATE FUNCTION [dbo].[GenerateAtom10]
(
@fd XML, -- Feed Information
@ent XML -- Entry Information
)
RETURNS XML
AS
BEGIN
-- This is the variable that will hold the result (ATOM feed)
DECLARE @atom XML

-- table variable to store feed information temporarily
DECLARE @feed TABLE
(
title VARCHAR(100),
subtitle VARCHAR(200),
id VARCHAR(100),
link VARCHAR(100),
generator VARCHAR(20),
updated DATETIME
)

-- table variabe to store item information temporarily
DECLARE @item TABLE
(
title VARCHAR(100),
link VARCHAR(100),
published DATETIME,
updated DATETIME,
content VARCHAR(1000),
authorname VARCHAR(30),
authorurl VARCHAR(100)
)

-- load feed information into the table variable
INSERT INTO @feed (title, subtitle, id, link, generator, updated)
SELECT
f.value('title[1]','VARCHAR(100)') AS Title,
f.value('subtitle[1]','VARCHAR(200)') AS Subtitle,
f.value('id[1]','VARCHAR(100)') AS ID,
f.value('link[1]','VARCHAR(100)') AS Link,
f.value('generator[1]','VARCHAR(20)') AS Generator,
f.value('updated[1]','DATETIME') AS Updated
FROM @fd.nodes('/Feed') feed(f)

-- load item information into the table variable
INSERT INTO @item (title, link, published, updated, content, authorname, authorurl)
SELECT
e.value('title[1]','VARCHAR(100)') AS Title,
e.value('link[1]','VARCHAR(100)') AS Link,
e.value('published[1]','DATETIME') AS Published,
e.value('updated[1]','DATETIME') AS Updated,
e.value('content[1]','VARCHAR(1000)') AS Content,
e.value('authorname[1]','VARCHAR(30)') AS AuthorName,
e.value('authorurl[1]','VARCHAR(100)') AS AuthorURL
FROM @ent.nodes('/Entries/Entry') entry(e)

-- Let us generate the feed
;WITH XMLNAMESPACES(
DEFAULT 'http://www.w3.org/2005/Atom'
)
SELECT @atom = (
SELECT
'html' AS 'title/@type',
title,
'html' AS 'subtitle/@type',
subtitle,
id,
(
SELECT
'alternate' AS 'link/@rel',
'text/html' AS 'link/@type',
link AS 'link/@href'
FROM @feed FOR XML PATH(''), TYPE
),
(
SELECT
'self' AS 'link/@rel',
'application/atom+xml' AS 'link/@type',
id AS 'link/@href'
FROM @feed FOR XML PATH(''), TYPE
),
link AS 'generator/@uri',
'1.0' AS 'generator/@version',
generator,
CONVERT(VARCHAR(20),updated,127) + 'Z' AS updated,
(
SELECT
title,
'alternate' AS 'link/@rel',
'text/html' AS 'link/@type',
link AS 'link/@href',
link,
link AS 'id',
CONVERT(nvarchar,published,127) + 'Z' AS published,
CONVERT(nvarchar,updated,127) + 'Z' AS updated,
content,
authorname AS 'author/name',
authorurl AS 'author/uri'
FROM @item
FOR XML PATH('entry'), TYPE
)
FROM @feed
FOR XML PATH('feed'),TYPE
)
-- return the feed
RETURN @atom
END
Invoking The Function

Let us test the function to make sure that it produces a valid ATOM 1.0 Feed. The following example tries to generate a feed using the function we just created.

-- declare the variables
DECLARE @fd XML, @ent XML

-- create an XML document with Feed information
SELECT @fd = (
SELECT
'Welcome to XML Workshop' AS title,
'A collection of articles on SQL Server and XML' AS subtitle,
'http://...TSQLAtom10.xml' AS id,
'http://blog.sqlserver.me' AS link,
'FOR XML' AS generator,
GETDATE() AS updated
FOR XML PATH(''), ROOT('Feed')
)

-- create an XML document with Entry information
SELECT @ent = (
SELECT
'Sales Order Workshop' AS title,
'http://.../salesorderworkshop' AS link,
GETDATE() AS published,
GETDATE() AS updated,
'A series of 4 articles ...' AS content,
'Jacob Sebastian' AS authorname,
'http://blog.sqlserver.me' AS authorurl
FOR XML PATH ('Entry'), ROOT('Entries')
)

-- generate the feed
SELECT dbo.GenerateAtom10( @fd, @ent )
The function generates the following output, which is a valid ATOM 1.0 feed. Try validating it with the feed validator of your choice. I validated it with the online feed validator at www.feedvalidator.org and the feed got validated successfully.




A collection of articles on SQL Server and XML

http://...TSQLAtom10.xml
rel="alternate" type="text/html"
href="http://blog.sqlserver.me" />
rel="self" type="application/atom+xml"
href="http://...TSQLAtom10.xml" />
version="1.0">FOR XML
2009-02-02T09:03:38.Z


href="http://.../salesorderworkshop">
http://.../salesorderworkshop

http://.../salesorderworkshop
2009-02-02T09:03:38.003Z
2009-02-02T09:03:38.003Z
A series of 4 articles ...

Jacob Sebastian
http://blog.sqlserver.me



While testing this code, I came across a very strange error. The function started failing with an error that says "An error occurred while executing batch. Error message is: Invalid calling sequence: file stream must be initialized first.". If you see this error while running a FOR XML query, it might be caused by your anti-virus software. It looks like SQL Server tries to create a temp file to hold the XML data temporarily and the Antivirus programme did not like that. I had to disable McAfee protection temporarily to get this error fixed.

A Real Life Example

In the previous session, we saw a Real life example that generates an RSS 2.0 feed based on the data taken from the "pubs" sample database. Let us use the same data and try to generate an ATOM 1.0 feed using the function we created above.

Let us generate an ATOM 1.0 feed with details of the books written by each author. For the purpose of our example, we will take author Green Marjorie. First of all, we need to create two XML variables and store the feed and entry information into them. Then we need to pass those two XML values into the function we created, which in turn will generate an ATOM 1.0 feed.

DECLARE @fd XML, @ent XML

-- Load feed (channel) information
SELECT @fd = (
SELECT
au_lname + ' ' + au_fname + '''s Books' AS title,
'List of books written by ' + au_lname + ' ' + au_fname AS subtitle,
'http://blog.sqlserver.me/books/' + au_id AS id,
'http://blog.sqlserver.me/books/' + au_id AS link,
'TSQL ATOM Generator by Jacob' AS generator,
GETDATE() AS updated
FROM authors WHERE au_id = '213-46-8915'
FOR XML PATH(''), ROOT('Feed')
)

-- load entry (item) information
SELECT @ent = (
SELECT
t.title AS title,
'http://blog.sqlserver.me/books/mg/' + t.title_id AS link,
GETDATE() AS published,
GETDATE() AS updated,
t.notes AS contend,
au.au_lname + ' ' + au.au_fname AS authorname,
'http://blog.sqlserver.me/books/' + au.au_id AS authorurl
FROM titleauthor ta
INNER JOIN authors au ON au.au_id = ta.au_id
INNER JOIN titles t ON
ta.title_id = t.title_id
AND ta.au_id = '213-46-8915'
FOR XML PATH ('Entries'), ROOT('Entry')
)

-- generate ATOM 1.0 feed.
SELECT GenerateAtom10(@fd, @ent)
The above code produces the following XML output.




A collection of short articles on SQL Server and XML

http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml
href="http://blog.sqlserver.me" />
rel="self" type="application/atom+xml"
href="http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml" />
FOR XML
2005-10-14T03:17:00Z


href="http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop">
http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop

http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop
2005-11-24T00:25:00Z
2005-11-24T00:25:00Z
A series of 4 articles which explain
how to pass variable number of parameters to a stored procedure
using XML

Jacob Sebastian
http://blog.sqlserver.me




href="http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop">
http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop

http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop
2005-10-14T02:17:00Z
2005-10-14T02:17:00Z
A collection of short articles that
explain how to generate XML output using TSQL keyword FOR
XML.

Jacob Sebastian
http://blog.sqlserver.me



The XML document generated by the above function is a valid ATOM 1.0 feed.

The function uses XQuery to retrieve information from the XML variables. XQuery is case sensitive and hence you need to make sure that the elements are named with correct spelling and follows correct casing as given in the list below:

The feed parameter recognizes the following elements.

title
subtitle
id
link
generator
updated
The element names should EXACTLY match with the list given above. The function will ignore any element that does not follow the above naming rules. If you mis-spell a few elements, a feed will still be generated, but it may not be a valid ATOM 1.0 feed (as it would be missing some of the required elements).

The entry parameter recognizes the following elements.

title
link
published
updated
content
authorname
authorurl
Conclusions

In this session, we created a function that generates an ATOM 1.0 feed. The function takes two XML parameters containing the feed and entry information. A feed is then generated based on the information stored in the XML parameters.

About the author

Jacob Sebastian is a SQL Server MVP and blogs regulary at http://blog.sqlserver.me/ on SQL Server and XML related topics. You can find his linkedin profile here.

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.

XML Workshop 22 - A TSQL RSS Library

Introduction
In the last few sessions of XML Workshop we had been looking at ways of generating RSS/ATOM Feeds. You can find the previous sessions here. We have seen how to generate RSS and ATOM feeds in SQL Server 2005 as well as 2000. In the previous sessions, we have seen how to generate RSS and ATOM feeds using FOR XML PATH as well as FOR XML EXPLICIT. If you are working with SQL Server 2005 (and above), you can take advantage of FOR XML PATH and if you are still in SQL Server 2000, you can use FOR XML EXPLICIT.

Though we have seen two versions of the source code for RSS and ATOM each, it would be often a difficult task to write the TSQL code to generate a correct RSS or ATOM feed taking data from a given set of tables. To make this task easier, in this session, we will create a function that generates an RSS feed from a given channel and item information. We will create a function that accepts two XML parameters (channel and items) and generate the required feed structure and returns an XML document.

So the focus of this sessions will be writing a function that accepts two XML parameters containing channel and item information and generates an RSS 2.0 feed. We will be able to call the function as in the given example.

-- declare the variables
DECLARE @ch XML, @itm XML

-- create an XML document with channel information
SELECT @ch = (
SELECT * FROM ChannelTable
FOR XML PATH(''), ROOT('Channel')
)

-- create an XML document with items information
SELECT @itm = (
SELECT * FROM Products
FOR XML PATH ('Items'), ROOT('Item')
)

-- generate the feed
SELECT dbo.GenerateRss20( @ch, @itm )
Function that generates RSS feed
Let us look at the function that generates an RSS Feed. The code is pretty much the same as what we developed in the previous sessions. The only difference is the part that transforms the XML parameters to virtual tables and runs a FOR XML PATH query on it to produce the RSS Feed. Here is the definition of the function.

CREATE FUNCTION GenerateRss20
(
@ch XML, -- Channel Information
@itm XML -- Item Information
)
RETURNS XML
AS
BEGIN
-- This is the variable that will hold the result (RSS feed)
DECLARE @rss XML

/*
To make the process easier, let us transform Channel and Item
information to a virtual table using CTE.
*/
;WITH channel AS (
SELECT
c.value('Title[1]','VARCHAR(500)') AS Title,
c.value('Link[1]','VARCHAR(500)') AS Link,
c.value('Description[1]','VARCHAR(MAX)') AS Description,
c.value('Webmaster[1]','VARCHAR(50)') AS Webmaster,
c.value('Language[1]','VARCHAR(20)') AS Language,
c.value('ImageUrl[1]','VARCHAR(500)') AS ImageUrl,
c.value('ImageTitle[1]','VARCHAR(500)') AS ImageTitle,
c.value('ImageLink[1]','VARCHAR(500)') AS ImageLink,
c.value('ImageWidth[1]','INT') AS ImageWidth,
c.value('ImageHeight[1]','INT') AS ImageHeight,
c.value('CopyRight[1]','VARCHAR(100)') AS CopyRight,
c.value('LastBuildDate[1]','DATETIME') AS LastBuildDate,
c.value('Ttl[1]','INT') AS Ttl
FROM @ch.nodes('/Channel') ch(c)
), items AS (
SELECT
i.value('Title[1]','VARCHAR(500)') AS Title,
i.value('Link[1]','VARCHAR(500)') AS Link,
i.value('Description[1]','VARCHAR(MAX)') AS Description,
i.value('Guid[1]','VARCHAR(500)') AS Guid,
i.value('PubDate[1]','DATETIME') AS PubDate
FROM @itm.nodes('/Item/Items') itm(i)
)
/*
Generate the RSS feed and assign to the local variable
*/
SELECT @rss = (
SELECT
'2.0' AS '@version',
(
SELECT
Title AS title,
Link AS link,
Description AS description,
Webmaster AS webMaster,
ISNULL(Language, 'en-us') AS language,
ImageUrl AS 'image/url',
ImageTitle AS 'image/title',
ImageLink AS 'image/link',
ImageWidth AS 'image/width',
ImageHeight AS 'image/height',
CopyRight AS copyright,
LEFT(DATENAME(dw, ISNULL(LastBuildDate,GETDATE())),3) + ', ' +
STUFF(CONVERT(nvarchar,ISNULL(LastBuildDate,GETDATE()),113),21,4,' GMT')
AS lastBuildDate,
Ttl AS ttl,
(
SELECT
Title AS title,
Link AS link,
Description AS description,
CASE
WHEN ISNULL(guid, Link) IS NULL THEN NULL
ELSE 'true'
END AS 'guid/@isPermaLink',
ISNULL(Guid, Link) AS guid,
LEFT(DATENAME(dw, ISNULL(PubDate,GETDATE())),3) + ', ' +
STUFF(CONVERT(nvarchar,ISNULL(PubDate,GETDATE()),113),21,4,' GMT')
AS pubDate
FROM Items FOR XML PATH('item'), TYPE
)
FROM channel
FOR XML PATH('channel'), TYPE
)
FOR XML PATH('rss')
)
-- return the feed
RETURN @rss
END
Invoking The Function
We have the function ready. Let us see a few examples that invoke the function and generate RSS feeds. Here is a basic example.

-- declare variables
DECLARE @ch XML, @itm XML

-- Create an XML document with channel information
SELECT @ch = (
SELECT
'TSQL RSS Library' AS Title,
'http://www.sqlserverandxml.com' AS Link,
'A TSQL RSS Library to help generating RSS 2.0 feeds' AS Description
FOR XML PATH(''), ROOT('Channel')
)

-- Create an XML document with item information
SELECT @itm = (
SELECT
'Item 1' AS Title,
'http://www.sqlserverandxml.com/1' AS Link,
'This is Item 1' AS Description
FOR XML PATH ('Items'), ROOT('Item')
)

-- generate the feed
SELECT dbo.GenerateRss20( @ch, @itm )



http://www.sqlserverandxml.com
A TSQL RSS Library to help generating RSS 2.0 feeds
en-us
Sat, 05 Jul 2008 15:07:45 GMT


http://www.sqlserverandxml.com/1
This is Item 1
http://www.sqlserverandxml.com/1
Sat, 05 Jul 2008 15:07:45 GMT



A Real Life Example
We just saw a basic sample that generates an RSS feed using the function we created. Let us now look at a real life example. We will use the pubs sample database for this example. Connect to the pubs database and create the function.

Assume that we need to generate an RSS feed for each author. The feed will contain information about the books written by each author. For the purpose of our example, we will take author Green Marjorie.

We need to create two XML variables before we can call the function. The first XML variable should contain channel information and the second should contain item information. Let us create the an XML document with channel information for author Green Marjorie. The information of authors is stored in the table 'Authors'. The Author ID of 'Green Marjorie' is '213-46-8915' and we will use it for identifying the correct row from the 'Authors' table.

DECLARE @ch XML

SELECT @ch = (
SELECT
au_lname + ' ' + au_fname + '''s Books' AS Title,
'http://www.sqlserverandxml.com/books/' + au_id AS Link,
'Books written by ' + au_lname + ' ' + au_fname AS Description
FROM authors WHERE au_id = '213-46-8915'
FOR XML PATH(''), ROOT('Channel')
)
Now, let us find information about the books of the above author. ID of books written by each author is stored in the table 'TitleAuthor'. Details of the book is stored in the table 'Titles'. Let us link these tables and retrieve information about the books written by 'Green Marjorie'.

DECLARE @itm XML

-- Create an XML document with item information
SELECT @itm = (
SELECT
t.title AS Title,
'http://www.sqlserverandxml.com/books/mg/' + t.title_id AS Link,
t.notes AS Description
FROM titleauthor ta
INNER JOIN titles t ON
ta.title_id = t.title_id
AND ta.au_id = '213-46-8915'
FOR XML PATH ('Items'), ROOT('Item')
)
The following code generates an RSS feed containing details of books written by Green Marjorie.

-- declare variables
DECLARE @ch XML, @itm XML

-- Create an XML document with channel information
SELECT @ch = (
SELECT
au_lname + ' ' + au_fname + '''s Books' AS Title,
'http://www.sqlserverandxml.com/books/' + au_id AS Link,
'Books written by ' + au_lname + ' ' + au_fname AS Description
FROM authors WHERE au_id = '213-46-8915'
FOR XML PATH(''), ROOT('Channel')
)

-- Create an XML document with item information
SELECT @itm = (
SELECT
t.title AS Title,
'http://www.sqlserverandxml.com/books/mg/' + t.title_id AS Link,
t.notes AS Description
FROM titleauthor ta
INNER JOIN titles t ON
ta.title_id = t.title_id
AND ta.au_id = '213-46-8915'
FOR XML PATH ('Items'), ROOT('Item')
)

-- generate the feed
SELECT dbo.GenerateRss20( @ch, @itm )



http://www.sqlserverandxml.com/books/213-46-8915
Books written by Green Marjorie
en-us
Sat, 05 Jul 2008 15:26:48 GMT


http://www.sqlserverandxml.com/books/mg/BU1032

An overview of available database systems with
emphasis on common business applications. Illustrated.


http://www.sqlserverandxml.com/books/mg/BU1032

Sat, 05 Jul 2008 15:26:48 GMT



http://www.sqlserverandxml.com/books/mg/BU2075

The latest medical and psychological techniques for living
with the electronic office. Easy-to-understand explanations.


http://www.sqlserverandxml.com/books/mg/BU2075

Sat, 05 Jul 2008 15:26:48 GMT



Note that the nodes of the XML parameters should follow certain naming rules. The function identifies the elements by applying an exact match on the name and hence the elements of your XML parameter should be correctly named. The channel parameter recognizes the following elements.

Title
Link
Description
Webmaster
Language
ImageUrl
ImageTitle
ImageLink
ImageWidth
ImageHeight
CopyRight
LastBuildDate
Ttl
The element names should EXACTLY match with the list given above. The function will ignore any elements that it cannot recognize. If you mis-spell a few elements, a feed will still be generated, but it may not be a valid RSS feed (as it would be missing some of the elements).

Just like the 'channel' parameter, the 'item' parameter too, expects the elements to follow certain naming rules. The 'item' parameter expects the following elements

Title
Link
Description
Guid
PubDate
Conclusions
In this session, we created a function that generates an RSS 2.0 feed. The function takes two XML parameters containing the channel and item information. A feed is then generated based on the information stored in the XML parameters

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

Introduction
The last few sessions of XML Workshop were focussing on generating RSS and ATOM feeds. Web Feeds (RSS or ATOM) are a must-have component of any modern web site. 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.

You can find the previous sessions on RSS/ATOM feed generation here. All the previous sessions of XML Workshop are listed here. Several people asked me for an RSS feed for the XML Workshop series and I have created one here.

Let us move to the sample feed and sample data. Then we will start writing the FOR XML EXPLICIT code needed to generate the sample feed based on the sample data.

Sample Feed
This is the feed that we are trying to generate with FOR XML EXPLICIT.


http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml
2005-10-14T03:17:00Z


A collection of short articles on SQL Server and XML


href="http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml" />

FOR XML



http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop
2005-11-24T00:25:00Z
2005-11-24T00:25:00Z

A series of 4 articles which explain
how to pass variable number of parameters to a stored procedure
using XML

href="http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop">
http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop

Jacob Sebastian
http://www.sqlserverandxml.com




http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop
2005-10-14T02:17:00Z
2005-10-14T02:17:00Z

A collection of short articles that
explain how to generate XML output using TSQL keyword FOR
XML.

href="http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop">
http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop

Jacob Sebastian
http://www.sqlserverandxml.com/



Now let us have a look at the tables and the sample data. Here is the script to generate the sample tables.

-- table for the feed information
IF OBJECT_ID('feed') IS NOT NULL DROP TABLE feed
CREATE TABLE feed(
title VARCHAR(100),
subtitle VARCHAR(200),
id VARCHAR(100),
link VARCHAR(100),
generator VARCHAR(20),
updated DATETIME )
GO

IF OBJECT_ID('entry') IS NOT NULL DROP TABLE entry
-- table to store the entries
CREATE TABLE entry(
entryID INT IDENTITY,
title VARCHAR(100),
link VARCHAR(100),
published DATETIME,
updated DATETIME,
content VARCHAR(1000),
authorname VARCHAR(30),
authorurl VARCHAR(100))
GO
And here is script to populate the tables with some sample data.

-- populate the 'feed' table
INSERT INTO feed (
title,
subtitle,
id,
link,
generator,
updated )
SELECT
'Welcome to XML Workshop',
'A collection of short articles on SQL Server and XML',
'http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml',
'http://www.sqlserverandxml.com/',
'FOR XML',
'2005-10-14T03:17:00'

-- populate the 'entry' table
INSERT INTO entry(
title,
link,
published,
updated,
content,
authorname,
authorurl )
SELECT
'Sales Order Workshop',
'http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop',
'2005-11-24T00:25:00',
'2005-11-24T00:25:00',
'A series of 4 articles which explain
how to pass variable number of parameters to a stored procedure
using XML',
'Jacob Sebastian',
'http://www.sqlserverandxml.com'
UNION ALL
SELECT
'FOR XML Workshop',
'http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop',
'2005-10-14T02:17:00',
'2005-10-14T02:17:00',
'A collection of short articles that
explain how to generate XML output using TSQL keyword FOR
XML.',
'Jacob Sebastian',
'http://www.sqlserverandxml.com/'
We have got the sample tables and sample data. Let us move ahead and write the code. We will develop the entire code in several small steps. We will start with a basic piece of code and will enhance it at every step. There will be some repetition, but I think it will help to understand FOR XML EXPLICIT behavior better.

Step 1 - Create the root element
At the step, lets create root element of the feed. The query is quite simple.

SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'feed!1!',
'http://www.w3.org/2005/Atom' AS 'feed!1!xmlns'
FROM feed
FOR XML EXPLICIT

Step 2
In the previous step we created the root element "feed". At this step, we will generate the child elements of "feed". "feed" has two types of child elements. Some of the child elements have their own children and others do not have. In this step, lets generate "id" and "updated", the two child elements of "feed" that do not have other children.

SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'feed!1!',
'http://www.w3.org/2005/Atom' AS 'feed!1!xmlns',
id AS 'feed!1!id!element',
CONVERT(nvarchar,updated,127) + 'Z' AS 'feed!1!updated!element'
FROM feed
FOR XML EXPLICIT

http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml
2005-10-14T03:17:00Z

Step 3
Some of the children of "feed" do have their own children. One such example is the "title" element, which has an attribute: "type".

SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'feed!1!',
'http://www.w3.org/2005/Atom' AS 'feed!1!xmlns',
id AS 'feed!1!id!element',
CONVERT(nvarchar,updated,127) + 'Z' AS 'feed!1!updated!element',
NULL AS 'title!2!',
NULL AS 'title!2!type'
FROM feed
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
title, 'html'
FROM feed
FOR XML EXPLICIT

http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml
2005-10-14T03:17:00Z


Step 4
Now let us generate the "subtitle" element.

SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'feed!1!',
'http://www.w3.org/2005/Atom' AS 'feed!1!xmlns',
id AS 'feed!1!id!element',
CONVERT(nvarchar,updated,127) + 'Z' AS 'feed!1!updated!element',
NULL AS 'title!2!',
NULL AS 'title!2!type',
NULL AS 'subtitle!3',
NULL AS 'subtitle!3!type'
FROM feed
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
title, 'html',
NULL, NULL
FROM feed
UNION ALL
SELECT
3 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
subtitle, 'html'
FROM feed
FOR XML EXPLICIT

http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml
2005-10-14T03:17:00Z


A collection of short articles on SQL Server and XML


Step 5
Generating the "link" element.

SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'feed!1!',
'http://www.w3.org/2005/Atom' AS 'feed!1!xmlns',
id AS 'feed!1!id!element',
CONVERT(nvarchar,updated,127) + 'Z' AS 'feed!1!updated!element',
NULL AS 'title!2!',
NULL AS 'title!2!type',
NULL AS 'subtitle!3',
NULL AS 'subtitle!3!type',
NULL AS 'link!4!rel',
NULL AS 'link!4!type',
NULL AS 'link!4!href'
FROM feed
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
title, 'html',
NULL, NULL,
NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
3 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
subtitle, 'html',
NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
4 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
'alternate', 'text/html', link
FROM feed
FOR XML EXPLICIT

http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml
2005-10-14T03:17:00Z


A collection of short articles on SQL Server and XML

href="http://www.sqlserverandxml.com/" />

Step 6
Since we need two link elements ("self" and "alternate"), we need to add one more UNION ALL.

SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'feed!1!',
'http://www.w3.org/2005/Atom' AS 'feed!1!xmlns',
id AS 'feed!1!id!element',
CONVERT(nvarchar,updated,127) + 'Z'
AS 'feed!1!updated!element',
NULL AS 'title!2!',
NULL AS 'title!2!type',
NULL AS 'subtitle!3',
NULL AS 'subtitle!3!type',
NULL AS 'link!4!rel',
NULL AS 'link!4!type',
NULL AS 'link!4!href'
FROM feed
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
title, 'html',
NULL, NULL,
NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
3 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
subtitle, 'html',
NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
4 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
'alternate', 'text/html', link
FROM feed
UNION ALL
SELECT
4 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
'self', 'application/atom+xml', id
FROM feed
FOR XML EXPLICIT

http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml
2005-10-14T03:17:00Z


A collection of short articles on SQL Server and XML

href="http://www.sqlserverandxml.com/" />
href="http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml" />

Step 7
Let us write the code for adding the "generator" element.

SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'feed!1!',
'http://www.w3.org/2005/Atom' AS 'feed!1!xmlns',
id AS 'feed!1!id!element',
CONVERT(nvarchar,updated,127) + 'Z'
AS 'feed!1!updated!element',
NULL AS 'title!2!',
NULL AS 'title!2!type',
NULL AS 'subtitle!3',
NULL AS 'subtitle!3!type',
NULL AS 'link!4!rel',
NULL AS 'link!4!type',
NULL AS 'link!4!href',
NULL AS 'generator!5',
NULL AS 'generator!5!uri',
NULL AS 'generator!5!version'
FROM feed
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
title, 'html',
NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
3 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
subtitle, 'html',
NULL, NULL, NULL,
NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
4 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
'alternate', 'text/html', link,
NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
4 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
'self', 'application/atom+xml', id,
NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
5 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL, NULL,
generator, link, '1.0'
FROM feed
FOR XML EXPLICIT

http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml
2005-10-14T03:17:00Z


A collection of short articles on SQL Server and XML

href="http://www.sqlserverandxml.com/" />
href="http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml" />

FOR XML


Step 8
We are done with the "feed" element. Let us now start adding the "entries". Some of the children of "entry" have children and others do not. At this step, let us generate the elements, which do not have children.

SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'feed!1!',
'http://www.w3.org/2005/Atom' AS 'feed!1!xmlns',
id AS 'feed!1!id!element',
CONVERT(nvarchar,updated,127) + 'Z'
AS 'feed!1!updated!element',
NULL AS 'title!2!',
NULL AS 'title!2!type',
NULL AS 'subtitle!3',
NULL AS 'subtitle!3!type',
NULL AS 'link!4!rel',
NULL AS 'link!4!type',
NULL AS 'link!4!href',
NULL AS 'generator!5',
NULL AS 'generator!5!uri',
NULL AS 'generator!5!version',
NULL AS 'entry!6!title!element',
NULL AS 'entry!6!id!element',
NULL AS 'entry!6!published!element',
NULL AS 'entry!6!updated!element',
NULL AS 'entry!6!content!element'
FROM feed
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
title, 'html',
NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
3 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
subtitle, 'html',
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
4 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
'alternate', 'text/html', link,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
4 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
'self', 'application/atom+xml', id,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
5 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL, NULL,
generator, link, '1.0',
NULL, NULL, NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
6 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
title, link, CONVERT(nvarchar,published,127) + 'Z',
CONVERT(nvarchar,updated,127) + 'Z', content
FROM entry
FOR XML EXPLICIT

http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml
2005-10-14T03:17:00Z


A collection of short articles on SQL Server and XML

href="http://www.sqlserverandxml.com/" />
href="http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml" />

FOR XML



http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop
2005-11-24T00:25:00Z
2005-11-24T00:25:00Z

A series of 4 articles which explain
how to pass variable number of parameters to a stored procedure
using XML




http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop
2005-10-14T02:17:00Z
2005-10-14T02:17:00Z

A collection of short articles that
explain how to generate XML output using TSQL keyword FOR
XML.



Step 9
Let us add the "link" element.

SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'feed!1!',
'http://www.w3.org/2005/Atom' AS 'feed!1!xmlns',
id AS 'feed!1!id!element',
CONVERT(nvarchar,updated,127) + 'Z'
AS 'feed!1!updated!element',
NULL AS 'title!2!',
NULL AS 'title!2!type',
NULL AS 'subtitle!3',
NULL AS 'subtitle!3!type',
NULL AS 'link!4!rel',
NULL AS 'link!4!type',
NULL AS 'link!4!href',
NULL AS 'generator!5',
NULL AS 'generator!5!uri',
NULL AS 'generator!5!version',
NULL AS 'entry!6!title!element',
NULL AS 'entry!6!id!element',
NULL AS 'entry!6!published!element',
NULL AS 'entry!6!updated!element',
NULL AS 'entry!6!content!element',
NULL AS 'link!7!',
NULL AS 'link!7!rel',
NULL AS 'link!7!type',
NULL AS 'link!7!href'
FROM feed
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
title, 'html',
NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
3 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
subtitle, 'html',
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
4 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
'alternate', 'text/html', link,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
4 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
'self', 'application/atom+xml', id,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
5 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL, NULL,
generator, link, '1.0',
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL
FROM feed
UNION ALL
SELECT
6 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
title, link, CONVERT(nvarchar,published,127) + 'Z',
CONVERT(nvarchar,updated,127) + 'Z', content,
NULL, NULL, NULL, NULL
FROM entry
UNION ALL
SELECT
7 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
link, 'alternate', 'text/html', link
FROM entry
FOR XML EXPLICIT

http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml
2005-10-14T03:17:00Z


A collection of short articles on SQL Server and XML

href="http://www.sqlserverandxml.com/" />
href="http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml" />

FOR XML



http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop
2005-11-24T00:25:00Z
2005-11-24T00:25:00Z

A series of 4 articles which explain
how to pass variable number of parameters to a stored procedure
using XML




http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop
2005-10-14T02:17:00Z
2005-10-14T02:17:00Z

A collection of short articles that
explain how to generate XML output using TSQL keyword FOR
XML.


href="http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop">
http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop

href="http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop">
http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop


Well, we landed with the sorting problem again. We saw this in the previous sessions where we discussed FOR XML EXPLICIT. When using FOR XML EXPLICIT, we need to make sure that the query returns rows in the same order as you need them in the XML document. You will see that the above XML is not correct, because the "link" element of both "entry" nodes are positioned at the bottom of the XML document. Each "link" element should be placed inside its own "entry" node. To achieve this, we need to add some kind of sorting logic to the query. We need to make sure that the row for the "link" element of the first "entry" should appear immediately after the row of the first "entry".

Step 10
Let us add some sorting logic to make sure that the query returns rows in the correct order.

SELECT
Tag,
Parent,
[feed!1!],
[feed!1!xmlns],
[feed!1!id!element],
[feed!1!updated!element],
[title!2!],
[title!2!type],
[subtitle!3],
[subtitle!3!type],
[link!4!rel],
[link!4!type],
[link!4!href],
[generator!5],
[generator!5!uri],
[generator!5!version],
[entry!6!title!element],
[entry!6!id!element],
[entry!6!published!element],
[entry!6!updated!element],
[entry!6!content!element],
[link!7!],
[link!7!rel],
[link!7!type],
[link!7!href]
FROM (
SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'feed!1!',
'http://www.w3.org/2005/Atom' AS 'feed!1!xmlns',
id AS 'feed!1!id!element',
CONVERT(nvarchar,updated,127) + 'Z'
AS 'feed!1!updated!element',
NULL AS 'title!2!',
NULL AS 'title!2!type',
NULL AS 'subtitle!3',
NULL AS 'subtitle!3!type',
NULL AS 'link!4!rel',
NULL AS 'link!4!type',
NULL AS 'link!4!href',
NULL AS 'generator!5',
NULL AS 'generator!5!uri',
NULL AS 'generator!5!version',
NULL AS 'entry!6!title!element',
NULL AS 'entry!6!id!element',
NULL AS 'entry!6!published!element',
NULL AS 'entry!6!updated!element',
NULL AS 'entry!6!content!element',
NULL AS 'link!7!',
NULL AS 'link!7!rel',
NULL AS 'link!7!type',
NULL AS 'link!7!href',
0 AS EntryID, 0 AS Sort
FROM feed
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
title, 'html',
NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
0, 0
FROM feed
UNION ALL
SELECT
3 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
subtitle, 'html',
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
0, 0
FROM feed
UNION ALL
SELECT
4 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
'alternate', 'text/html', link,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
0, 0
FROM feed
UNION ALL
SELECT
4 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
'self', 'application/atom+xml', id,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
0, 0
FROM feed
UNION ALL
SELECT
5 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL, NULL,
generator, link, '1.0',
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
0, 0
FROM feed
UNION ALL
SELECT
6 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
title, link, CONVERT(nvarchar,published,127) + 'Z',
CONVERT(nvarchar,updated,127) + 'Z', content,
NULL, NULL, NULL, NULL,
entryID, 1
FROM entry
UNION ALL
SELECT
7 AS Tag,
6 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
link, 'alternate', 'text/html', link,
entryID, 2
FROM entry
) A
ORDER BY EntryID, Sort
FOR XML EXPLICIT

http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml
2005-10-14T03:17:00Z


A collection of short articles on SQL Server and XML


href="http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml" />

FOR XML



http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop
2005-11-24T00:25:00Z
2005-11-24T00:25:00Z

A series of 4 articles which explain
how to pass variable number of parameters to a stored procedure
using XML

href="http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop">
http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop




http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop
2005-10-14T02:17:00Z
2005-10-14T02:17:00Z

A collection of short articles that
explain how to generate XML output using TSQL keyword FOR
XML.

href="http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop">
http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop



Step 11
We are almost done. This is the last step and we need to generate the "author" element.

SELECT
Tag,
Parent,
[feed!1!],
[feed!1!xmlns],
[feed!1!id!element],
[feed!1!updated!element],
[title!2!],
[title!2!type],
[subtitle!3],
[subtitle!3!type],
[link!4!rel],
[link!4!type],
[link!4!href],
[generator!5],
[generator!5!uri],
[generator!5!version],
[entry!6!title!element],
[entry!6!id!element],
[entry!6!published!element],
[entry!6!updated!element],
[entry!6!content!element],
[link!7!],
[link!7!rel],
[link!7!type],
[link!7!href],
[author!8!name!element],
[author!8!uri!element]
FROM (
SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'feed!1!',
'http://www.w3.org/2005/Atom' AS 'feed!1!xmlns',
id AS 'feed!1!id!element',
CONVERT(nvarchar,updated,127) + 'Z'
AS 'feed!1!updated!element',
NULL AS 'title!2!',
NULL AS 'title!2!type',
NULL AS 'subtitle!3',
NULL AS 'subtitle!3!type',
NULL AS 'link!4!rel',
NULL AS 'link!4!type',
NULL AS 'link!4!href',
NULL AS 'generator!5',
NULL AS 'generator!5!uri',
NULL AS 'generator!5!version',
NULL AS 'entry!6!title!element',
NULL AS 'entry!6!id!element',
NULL AS 'entry!6!published!element',
NULL AS 'entry!6!updated!element',
NULL AS 'entry!6!content!element',
NULL AS 'link!7!',
NULL AS 'link!7!rel',
NULL AS 'link!7!type',
NULL AS 'link!7!href',
NULL AS 'author!8!name!element',
NULL AS 'author!8!uri!element',
0 AS EntryID, 0 AS Sort
FROM feed
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
title, 'html',
NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
NULL, NULL,
0, 0
FROM feed
UNION ALL
SELECT
3 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
subtitle, 'html',
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
NULL, NULL,
0, 0
FROM feed
UNION ALL
SELECT
4 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
'alternate', 'text/html', link,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
NULL, NULL,
0, 0
FROM feed
UNION ALL
SELECT
4 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
'self', 'application/atom+xml', id,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
NULL, NULL,
0, 0
FROM feed
UNION ALL
SELECT
5 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL, NULL,
generator, link, '1.0',
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
NULL, NULL,
0, 0
FROM feed
UNION ALL
SELECT
6 AS Tag,
1 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
title, link, CONVERT(nvarchar,published,127) + 'Z',
CONVERT(nvarchar,updated,127) + 'Z', content,
NULL, NULL, NULL, NULL,
NULL, NULL,
entryID, 1
FROM entry
UNION ALL
SELECT
7 AS Tag,
6 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
link, 'alternate', 'text/html', link,
NULL, NULL,
entryID, 2
FROM entry
UNION ALL
SELECT
8 AS Tag,
6 AS Parent,
NULL, NULL, NULL, NULL,
NULL, NULL,
NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
authorname, authorurl,
entryID, 3
FROM entry
) A
ORDER BY EntryID, Sort
FOR XML EXPLICIT

http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml
2005-10-14T03:17:00Z


A collection of short articles on SQL Server and XML


href="http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml" />

FOR XML



http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop
2005-11-24T00:25:00Z
2005-11-24T00:25:00Z

A series of 4 articles which explain
how to pass variable number of parameters to a stored procedure
using XML

href="http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop">
http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop


Jacob Sebastian
http://www.sqlserverandxml.com




http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop
2005-10-14T02:17:00Z
2005-10-14T02:17:00Z

A collection of short articles that
explain how to generate XML output using TSQL keyword FOR
XML.

href="http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop">
http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop


Jacob Sebastian
http://www.sqlserverandxml.com/



Congratulations! We have the feed we wanted. You can validate the feed we just generated with FeedValidator.org or any of the validator of your choice. I have validated it only with FeedValidator.org. If your feed validator reports a problem with the above feed, please let me know.

Conclusions
We just saw another session on FOR XML EXPLICIT. Those of you who have good control over FOR XML EXPLICIT might find quite a lot of repetitive stuff here. FOR XML EXPLICIT seems to be very confusing to many people. The most complicated part is that people often land up with errors after writing a lengthy query. I am more in favor of taking "baby-steps" by using a step-by-step approach for writing FOR XML EXPLICIT queries. At each step we could add the code for a new element and test the results. If an error occurs, we could rollback to the previous step and quickly fix/spot the problem.

I have used this step-by-step approach to help many people who contacted me with strange errors/behaviors with lengthy FOR XML EXPLICIT queries. If you are a new-bie and trying to write a FOR XML EXPLICIT query, I would suggest using a step-by-step approach as given in this session.