Sqlserver and XML

FOR XML EXPLICIT – Part 1

<Agents>

With SQL Server 2005 we can generate XML output using different methods. Using TSQL keyword FOR XML along with AUTO, RAW, PATH and EXPLICIT we could generate almost any XML structure that we might need. PATH is a very powerful keyword which allows a great deal of customization on the structure of the generated XML and is relatively easy to use. EXPLICIT provides more control over the generated XML structure but it is much more complex then other methods. Most of the times, we could generate the same output as EXPLICIT by using PATH. But some times, the structure of the XML output might be too complex for PATH to generate, and we will have to go with EXPLICIT.

PATH is available only in SQL Server 2005. If you are working with SQL server 2000, you will have to work with EXPLICIT if you need control over the XML structure being generated. I had been helping some people on writing TSQL queries with EXPLICIT recently, at some of the Internet forums. My observation is that most of the times people get an error because of the sort order of the result set being passed to FOR XML EXPLICIT

I worked with Vimal Rughani recently on such a query. After we wrote the query, he asked me if I could explain the flow of the code. I thought that it would be a good idea to write down the steps I went through while writing the query, so that it will help other people around too. He wanted to generate the following XML output using FOR XML EXPLICIT.

The data should come from two tables Agents and Addresses. Before we write the query, we need to create those tables and populate them with some data. For the purpose of this example, we may not need any physical tables. We could go with memory tables. The following code will create two memory tables and fill them with data. The code is written by Kent in one of the MSDN forums.

Let us start writing the query. Because we write this query for learning purpose,
I would like to take an approach by which we will progressively develop the complete
query.

So let us start with the root node. Let us first create the query for generating the root node.

This will generate the root node that we need.

Now let us write the code for generating next level. The next level is the agent node. This information should come from the agent table. Let us  add the code for that.

Note the code in yellow. This is what we added to the previous version. This query generates the following output.

Good so far. Let us add fname and ssn under the agent node as child elements.

This version will give us the following output.

Due to some unknown reasons, Windows Live Writer does not allow me to write further in this post. Hence I am putting the rest of the code to another post.

Posted in Uncategorized |

XML Workshop

1. XML Workshop I – Generating XML with FOR XML
2. XML Workshop II – Reading values from XML variables
3. XML Workshop III – FOR XML PATH
4. XML Workshop IV – FOR XML EXPLICIT
5. XML Workshop V – Reading Values from XML Columns
6. XML Workshop VI – Typed XML and SCHEMA Collection
7. XML Workshop VII – Validating values with SCHEMA
8. XML Workshop VIII – Custom Types and Inheritance
9. XML Workshop IX – Mixed Types
10. XML Workshop X – Working with namespaces
11. XML Workshop XI – Default Namespaces
12. XML Workshop XII – Parsing a delimited string
13. XML Workshop XIII – XSD And Variable Content Containers
14. XML Workshop XIV – Generating an XML Tree
15. XML Workshop XV – Accessing FOR XML results with ADO.NET
16. XML Workshop XVI – Shaping the XML results
17. XML Workshop XVII – Writing a LOOP to process all XML nodes
18. XML Workshop XVIII – Generating an RSS 2.0 Feed with TSQL

Posted in Uncategorized |

SQLCE Workshop III – Getting started with 3.5 Beta

Provides a basic introduction to SQL CE 3.5 beta which involves installation, creating a database and tables, connecting to the database file and inserting records to the newly created table from VB.NET as well as C#.NET.

Read the article

Posted in Uncategorized |

SQL Server 2005 Replication – Adding two articles with same name in different schemas

I came across a very strange problem recently while creating a publication. I had objects with the same name but in different schemas. For example, “dbo.Invoices”, “Billing.Invoices”, “AR.Invoices” and “AP.Invoices”. In my case, the first object was a TABLE and the other 3 were VIEWS which selects data from the invoice table. I added “dbo.Invoices” to the publication and then tried to add the other 3 views. That is where the trouble started.

When I tried to add “Billing.Invoices”, I got an error which said “Article ‘Invoices’ already exists in the publication”. It looked like the UI (Property Page of Publication) does not consider the schema of the object being added.

I got the problem resolved by adding the article using TSQL instead of the UI page. I used the following code to add the new article to the publication.

exec sp_addarticle

@publication = N’MyPublicationName’,

@article = N’Billing_Invoices’,

–I gave a different name to the article

@source_owner = N’Billing’,

@source_object = N’Invoics’,

@type = N’view schema only’,

@description = N”,

@creation_script = N”,

@pre_creation_cmd = N’drop’,

@schema_option = 0×0000000008000001,

@destination_table = N’Invoices’,

@destination_owner = N’Billing’,

@status = 16

Posted in Uncategorized |

XML Workshop V – Reading Values from XML Columns

If you are new to XQuery, you might find it little tricky to read values from an XML column. Reading a value from an XML variable is slightly different from reading values from an XML column. In an XML variable we have a single XML document. But the case of an XML column is different. When you query a table, each row in the table has an XML document stored in it.

Let us look at an example. In the following example, we assign a value to an XML variable and Query it.

That was pretty much simple. You will need a slightly modified version of this query, if you need to read the values from an XML column. We need to use CROSS APPLY so that we can access more than one record unlike the previous example, where we had only one variable.

My XML Workshop V at SQLServerCentral presents a detailed tutorial which explains the usage of CROSS APPLY. It also presents a few different ways to query a table and retrieve different pieces of information.

Posted in Uncategorized |