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 = 0x0000000008000001,

      @destination_table = N'Invoices',

      @destination_owner = N'Billing',

      @status = 16

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

 

 

These days hosting services like webhosting net tend to provide a logo design in addition to the web site design provided according to search engine marketing criterias. Later software scripts and ip phones are added to the layout.

XML Workshop

1. XML Workshop I - Generating XML with FOR XML - A short article that explains how to generate XML output with TSQL keyword FOR XML with AUTO and RAW directives
2. XML Workshop II - Reading values from XML variables - This article explains how to read values from an XML variable using XQuery (TSQL)
3. XML Workshop III - FOR XML PATH - This article shows several examples that generates XML output using FOR XML with PATH
4. XML Workshop IV - FOR XML EXPLICIT - Explains the usage of EXPLICIT with FOR XML
5. XML Workshop V - Reading Values from XML Columns - This article gives a closer look into XQuery and shows examples of reading values from XML columns.
6. XML Workshop VI - Typed XML and SCHEMA Collection - Introduces TYPED XML and XML SCHEMA COLLECTIONS
7. XML Workshop VII - Validating values with SCHEMA - Shows how to do Data Type validations using an XML SCHEMA COLLECTION
8. XML Workshop VIII - Custom Types and Inheritance - This article explains how to create custom types and create types that inherit from other types. Usage of custom types can make the XML schema less complex and easier to understand and manage
9. XML Workshop IX - Mixed Types - This article introduces ComplexTypes having Mixed content model
10. XML Workshop X - Working with namespaces - Introduces XML namespaces and shows how to generate XML documents having namespace declaration using TSQL keyword WITH XMLNAMESPACES
11. XML Workshop XI - Default Namespaces - Introduces Default Namespaces
12. XML Workshop XII - Parsing a delimited string - This workshop builds a function that parses a delimited string using XQuery
13. XML Workshop XIII - XSD And Variable Content Containers - XSD performs very strict validation on the content of an XML element or attribute. This article explains how to define elements that can take variable content
14. XML Workshop XIV - Generating an XML Tree - Explains how to generate an XML tree using a recursive CTE and FOR XML EXPLICIT
15. XML Workshop XV - Accessing FOR XML results with ADO.NET - This article explains how to access the output of FOR XML from ADO.NET
16. XML Workshop XVI - Shaping the XML results - Shows a few more examples that generates XML output using FOR XML with AUTO, PATH and EXPLICIT
17. XML Workshop XVII - Writing a LOOP to process all XML nodes - Explains how to write a loop that iterates over all the nodes of an XML document
18. XML Workshop XVIII - Generating an RSS 2.0 Feed with TSQL - Shows how to generate an RSS 2.0 feed using FOR XML PATH
19. XML Workshop XIX - Generating an ATOM 1.0 Feed with TSQL - Shows how to generate an ATOM 1.0 feed using FOR XML PATH
20. XML Workshop XX - Generating an RSS 2.0 Feed with TSQL(SQL server 2000) - Explains how to generate an RSS 2.0 FEED with FOR XML EXPLICIT
21. XML Workshop XXI - Generating an ATOM 1.0 Feed with FOR XML EXPLICIT - Explains how to generate an ATOM 1.0 FEED with FOR XML EXPLICIT (SQL Server 2000)

FOR XML EXPLICIT - Part 1

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. 

<Agents>

  <Agent AgentID="1">

    <Fname>Vimal</Fname>

    <SSN>123-23-4521</SSN>

    <AddressCollection>

      <Address>

        <AddressType>Home</AddressType>

        <Address1>abc</Address1>

        <Address2>xyz road</Address2>

        <City>RJ</City>

      </Address>

      <Address>

        <AddressType>Office</AddressType>

        <Address1>temp</Address1>

        <Address2>ppp road</Address2>

        <City>RJ</City>

      </Address>

    </AddressCollection>

  </Agent>

  <Agent AgentID="2">

    <Fname>Jacob</Fname>

    <SSN>321-52-4562</SSN>

    <AddressCollection>

      <Address>

        <AddressType>Home</AddressType>

        <Address1>xxx</Address1>

        <Address2>aaa road</Address2>

        <City>NY</City>

      </Address>

      <Address>

        <AddressType>Office</AddressType>

        <Address1>ccc</Address1>

        <Address2>oli Com</Address2>

        <City>CL</City>

      </Address>

      <Address>

        <AddressType>Temp</AddressType>

        <Address1>eee</Address1>

        <Address2>olkiu road</Address2>

        <City>CL</City>

      </Address>

    </AddressCollection>

  </Agent>

  <Agent AgentID="3">

    <Fname>Tom</Fname>

    <SSN>252-52-4563</SSN>

    <AddressCollection>

      <Address>

        <AddressType>Home</AddressType>

        <Address1>ttt</Address1>

        <Address2>loik road</Address2>

        <City>NY</City>

      </Address>

    </AddressCollection>

  </Agent>

</Agents>

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.

/*

Borrowed from Kent's code

*/

declare @agent table

(

    AgentID int,

    Fname varchar(5),

    SSN varchar(11)

)

 

insert into @agent

select 1, 'Vimal', '123-23-4521' union all

select 2, 'Jacob', '321-52-4562' union all

select 3, 'Tom', '252-52-4563'

 

declare @address table

(

    AddressID int,

    AddressType varchar(12),

    Address1 varchar(20),

    Address2 varchar(20),

    City varchar(25),

    AgentID int

)

insert into @address

select 1, 'Home', 'abc', 'xyz road', 'RJ', 1 union all

select 2, 'Office', 'temp', 'ppp road', 'RJ', 1 union all

select 3, 'Home', 'xxx', 'aaa road', 'NY', 2 union all

select 4, 'Office', 'ccc', 'oli Com', 'CL', 2 union all

select 5, 'Temp', 'eee', 'olkiu road', 'CL', 2 union all

select 6, 'Home', 'ttt', 'loik road', 'NY', 3

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.

SELECT

    1 AS Tag,

    NULL AS Parent,

    NULL AS 'Agents!1!'

FOR XML EXPLICIT

This will generate the root node that we need.

<Agents />

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. 

SELECT

    1 AS Tag,

    NULL AS Parent,

    NULL AS 'Agents!1!',

    NULL AS 'Agent!2!AgentID'

UNION ALL

SELECT

    2 AS Tag,

    1 AS Parent,

    NULL,

    AgentID

FROM @agent

FOR XML EXPLICIT

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

<Agents>

  <Agent AgentID="1" />

  <Agent AgentID="2" />

  <Agent AgentID="3" />

</Agents>

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

SELECT

    1 AS Tag,

    NULL AS Parent,

    NULL AS 'Agents!1!',

    NULL AS 'Agent!2!AgentID',

    NULL AS 'Agent!2!Fname!Element',

    NULL AS 'Agent!2!SSN!Element'

UNION ALL

SELECT

    2 AS Tag,

    1 AS Parent,

    NULL,

    AgentID,

    Fname,

    SSN

FROM @agent

FOR XML EXPLICIT

This version will give us the following output.

<Agents>

  <Agent AgentID="1">

    <Fname>Vimal</Fname>

    <SSN>123-23-4521</SSN>

  </Agent>

  <Agent AgentID="2">

    <Fname>Jacob</Fname>

    <SSN>321-52-4562</SSN>

  </Agent>

  <Agent AgentID="3">

    <Fname>Tom</Fname>

    <SSN>252-52-4563</SSN>

  </Agent>

</Agents>

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.

Continued to part 2

Part 3

FOR XML EXPLICIT - Part 2

Continued from Part 1

Let us move ahead. Under each agent, we need a node named AddressCollection. Let us add the code for that. 

SELECT

    1 AS Tag,

    NULL AS Parent,

    NULL AS 'Agents!1!',

    NULL AS 'Agent!2!AgentID',

    NULL AS 'Agent!2!Fname!Element',

    NULL AS 'Agent!2!SSN!Element',

    NULL AS 'AddressCollection!3!Element'

UNION ALL

SELECT

    2 AS Tag, 1 AS Parent,

    NULL, AgentID, Fname, SSN,

    NULL

FROM @agent

UNION ALL

SELECT

    3 AS Tag, 2 AS Parent,

    NULL, NULL, NULL, NULL,

    NULL

FROM @agent

FOR XML EXPLICIT

We added a new level for AddressCollection element. I used FROM @agent because we need an AddressCollection element for each agent record. Here is the output.

<Agents>

  <Agent AgentID="1">

    <Fname>Vimal</Fname>

    <SSN>123-23-4521</SSN>

  </Agent>

  <Agent AgentID="2">

    <Fname>Jacob</Fname>

    <SSN>321-52-4562</SSN>

  </Agent>

  <Agent AgentID="3">

    <Fname>Tom</Fname>

    <SSN>252-52-4563</SSN>

    <AddressCollection />

    <AddressCollection />

    <AddressCollection />

  </Agent>

</Agents>

Wait a second! we have a problem. Note that the 3 AddressCollection elements were created as part of the last node. Why does this happen? To understand that we need to look at the query results that we passed to FOR XML EXPLICIT. Let us run the query without FOR XML EXPLICIT.

Tag	Parent	Agents!1!	Agent!2!AgentID	Agent!2!Fname	Agent!2!ssn	AddressCollection!3
1	NULL	NULL	NULL	NULL	NULL	NULL
2	1	NULL	1	Vimal	123-23-4521	NULL
2	1	NULL	2	Jacob	321-52-4562	NULL
2	1	NULL	3	Tom	252-52-4563	NULL
3	2	NULL	NULL	NULL	NULL	NULL
3	2	NULL	NULL	NULL	NULL	NULL
3	2	NULL	NULL	NULL	NULL	NULL

Note the rows in yellow . These are the records with tag 3. Note that they appear at the bottom of the result set. That is the reason why they appear at the bottom of the XML result. So to fix this, we need to change the order of the rows. So to get the correct XML we need to have the query results in the following order. 

Tag	Parent	Agents!1!	Agent!2!AgentID	Agent!2!Fname	Agent!2!ssn	AddressCollection!3
1	NULL	NULL	NULL	NULL	NULL	NULL
2	1	NULL	1	Vimal	123-23-4521	NULL
3	2	NULL	NULL	NULL	NULL	NULL
2	1	NULL	2	Jacob	321-52-4562	NULL
3	2	NULL	NULL	NULL	NULL	NULL
2	1	NULL	3	Tom	252-52-4563	NULL
3	2	NULL	NULL	NULL	NULL	NULL

So at this stage, we need to write some kind of code to alter the sort order of the records. There might be different ways to do that. What I did was to add a calculated column for the sort order based on the AgentID. Here is the new code. 

SELECT

    1 AS Tag,

    NULL AS Parent,

    0 AS Sort,

    NULL AS 'Agents!1!',

    NULL AS 'Agent!2!AgentID',

    NULL AS 'Agent!2!Fname!Element',

    NULL AS 'Agent!2!SSN!Element',

    NULL AS 'AddressCollection!3!Element'

UNION ALL

SELECT

    2 AS Tag, 1 AS Parent,

    AgentID * 100 AS Sort,

    NULL, AgentID, Fname, SSN,

    NULL

FROM @agent

UNION ALL

SELECT

    3 AS Tag, 2 AS Parent,

    AgentID * 100 + 1 AS Sort,

    NULL, NULL, NULL, NULL,

    NULL

FROM @agent

ORDER BY Sort

Tag	Parent	Sort	Agents!1!	Agent!2!AgentID	Agent!2!Fname	Agent!2!ssn	AddressCollection!3
1	NULL	0	NULL	NULL	NULL	NULL	NULL
2	1	100	NULL	1	Vimal	123-23-4521	NULL
3	2	101	NULL	NULL	NULL	NULL	NULL
2	1	200	NULL	2	Jacob	321-52-4562	NULL
3	2	201	NULL	NULL	NULL	NULL	NULL
2	1	300	NULL	3	Tom	252-52-4563	NULL
3	2	301	NULL	NULL	NULL	NULL	NULL

Well, that worked. Note that the sort order holds correct values so that we get the records in the desired order. There might be different ways to generate the sort order column. For the purpose of this example, I made it by multiplying the AgentID with 100, 101 etc. This approach may not work in a different situation. It worked for the example. The KEY here is to sort the records in the correct order (exactly in the order that we need them in the XML results). You can apply your own logic that you feel right, to achieve this.

Let us generate the XML now. 

SELECT

    1 AS Tag,

    NULL AS Parent,

    0 AS Sort,

    NULL AS 'Agents!1!',

    NULL AS 'Agent!2!AgentID',

    NULL AS 'Agent!2!Fname!Element',

    NULL AS 'Agent!2!SSN!Element',

    NULL AS 'AddressCollection!3!Element'

UNION ALL

SELECT

    2 AS Tag, 1 AS Parent,

    AgentID * 100 AS Sort,

    NULL, AgentID, Fname, SSN,

    NULL

FROM @agent

UNION ALL

SELECT

    3 AS Tag, 2 AS Parent,

    AgentID * 100 + 1 AS Sort,

    NULL, NULL, NULL, NULL,

    NULL

FROM @agent

ORDER BY Sort

FOR XML EXPLICIT

unfortunately, this code will not work. If you try to run this, you will get the following error.

TOSHIBA-USER\SQL2005(TOSHIBA-USER\Jacob Sebastian): Msg 6802, Level 16, State 1, Line 33

FOR XML EXPLICIT query contains the invalid column name 'Sort'. Use the TAGNAME!TAGID!ATTRIBUTENAME[!..] format where TAGID is a positive integer.

The error is caused by the "Sort" column that we just added. When we use FOR XML EXPLICIT, all columns other than "Tag" and "Parent" should be in the form of "[TAG]![TAGID]!ATTRIBUTE...". We need to hide the "Sort" column. Lets create an outer query to do this. 

SELECT Tag, Parent,

    [Agents!1!],

    [Agent!2!AgentID],

    [Agent!2!Fname!Element],

    [Agent!2!SSN!Element],

    [AddressCollection!3!Element]

FROM (

    SELECT

        1 AS Tag,

        NULL AS Parent,

        0 AS Sort,

        NULL AS 'Agents!1!',

        NULL AS 'Agent!2!AgentID',

        NULL AS 'Agent!2!Fname!Element',

        NULL AS 'Agent!2!SSN!Element',

        NULL AS 'AddressCollection!3!Element'

    UNION ALL

    SELECT

        2 AS Tag, 1 AS Parent,

        AgentID * 100 AS Sort,

        NULL, AgentID, Fname, SSN,

        NULL

    FROM @agent

    UNION ALL

    SELECT

        3 AS Tag, 2 AS Parent,

        AgentID * 100 + 1 AS Sort,

        NULL, NULL, NULL, NULL,

        NULL

    FROM @agent

) A

ORDER BY Sort

FOR XML EXPLICIT

Here is the result.

<Agents>

  <Agent AgentID="1">

    <Fname>Vimal</Fname>

    <SSN>123-23-4521</SSN>

    <AddressCollection />

  </Agent>

  <Agent AgentID="2">

    <Fname>Jacob</Fname>

    <SSN>321-52-4562</SSN>

    <AddressCollection />

  </Agent>

  <Agent AgentID="3">

    <Fname>Tom</Fname>

    <SSN>252-52-4563</SSN>

    <AddressCollection />

  </Agent>

</Agents>

Continued to part 3

FOR XML EXPLICIT - Part 3

Part 1

Part 2

Having fixed the problem with the sort order, let us go ahead with the rest of the code. Let us add Addresses under the AddressCollection node and come up with the final version of the code. We need to add a new level, Tag 4. Note that I used AgentID * 102 to make sure that this record will come right below the AddressCollection row of each Agent.

SELECT Tag, Parent,

    [Agents!1!],

    [Agent!2!AgentID],

    [Agent!2!Fname!Element],

    [Agent!2!SSN!Element],

    [AddressCollection!3!Element],

    [Address!4!AddressType!Element],

    [Address!4!Address1!Element],

    [Address!4!Address2!Element],

    [Address!4!City!Element]

FROM (

    SELECT

        1 AS Tag,

        NULL AS Parent,

        0 AS Sort,

        NULL AS 'Agents!1!',

        NULL AS 'Agent!2!AgentID',

        NULL AS 'Agent!2!Fname!Element',

        NULL AS 'Agent!2!SSN!Element',

        NULL AS 'AddressCollection!3!Element',

        NULL AS 'Address!4!AddressType!Element',

        NULL AS 'Address!4!Address1!Element',

        NULL AS 'Address!4!Address2!Element',

        NULL AS 'Address!4!City!Element'

    UNION ALL

    SELECT

        2 AS Tag,

        1 AS Parent,

        AgentID * 100,

        NULL, AgentID, Fname, SSN,

        NULL,NULL, NULL, NULL, NULL

        FROM @Agent

    UNION ALL

    SELECT

        3 AS Tag,

        2 AS Parent,

        AgentID * 100 + 1,

        NULL,NULL,NULL, NULL,

        NULL, NULL, NULL, NULL, NULL

    FROM @Agent

    UNION ALL

    SELECT

        4 AS Tag,

        3 AS Parent,

        AgentID * 100 + 2,

        NULL,NULL,NULL,NULL,NULL,

        AddressType, Address1, Address2, City

    FROM @Address

) A

ORDER BY Sort

FOR XML EXPLICIT

Here is the complete listing of the code.

/*

Borrowed from Kent's code

*/

declare @agent table

(

    AgentID int,

    Fname varchar(5),

    SSN varchar(11)

)

 

insert into @agent

select 1, 'Vimal', '123-23-4521' union all

select 2, 'Jacob', '321-52-4562' union all

select 3, 'Tom', '252-52-4563'

 

declare @address table

(

    AddressID int,

    AddressType varchar(12),

    Address1 varchar(20),

    Address2 varchar(20),

    City varchar(25),

    AgentID int

)

insert into @address

select 1, 'Home', 'abc', 'xyz road', 'RJ', 1 union all

select 2, 'Office', 'temp', 'ppp road', 'RJ', 1 union all

select 3, 'Home', 'xxx', 'aaa road', 'NY', 2 union all

select 4, 'Office', 'ccc', 'oli Com', 'CL', 2 union all

select 5, 'Temp', 'eee', 'olkiu road', 'CL', 2 union all

select 6, 'Home', 'ttt', 'loik road', 'NY', 3

/*

End Borrow

*/

 

SELECT Tag, Parent,

    [Agents!1!],

    [Agent!2!AgentID],

    [Agent!2!Fname!Element],

    [Agent!2!SSN!Element],

    [AddressCollection!3!Element],

    [Address!4!AddressType!Element],

    [Address!4!Address1!Element],

    [Address!4!Address2!Element],

    [Address!4!City!Element]

FROM (

    SELECT

        1 AS Tag,

        NULL AS Parent,

        0 AS Sort,

        NULL AS 'Agents!1!',

        NULL AS 'Agent!2!AgentID',

        NULL AS 'Agent!2!Fname!Element',

        NULL AS 'Agent!2!SSN!Element',

        NULL AS 'AddressCollection!3!Element',

        NULL AS 'Address!4!AddressType!Element',

        NULL AS 'Address!4!Address1!Element',

        NULL AS 'Address!4!Address2!Element',

        NULL AS 'Address!4!City!Element'

    UNION ALL

    SELECT

        2 AS Tag,

        1 AS Parent,

        AgentID * 100,

        NULL, AgentID, Fname, SSN,

        NULL,NULL, NULL, NULL, NULL

        FROM @Agent

    UNION ALL

    SELECT

        3 AS Tag,

        2 AS Parent,

        AgentID * 100 + 1,

        NULL,NULL,NULL, NULL,

        NULL, NULL, NULL, NULL, NULL

    FROM @Agent

    UNION ALL

    SELECT

        4 AS Tag,

        3 AS Parent,

        AgentID * 100 + 2,

        NULL,NULL,NULL,NULL,NULL,

        AddressType, Address1, Address2, City

    FROM @Address

) A

ORDER BY Sort

FOR XML EXPLICIT

 

/*

OUTPUT:

<Agents>

    <Agent AgentID="1">

        <Fname>Vimal</Fname>

        <SSN>123-23-4521</SSN>

        <AddressCollection>

            <Address>

                <AddressType>Home</AddressType>

                <Address1>abc</Address1>

                <Address2>xyz road</Address2>

                <City>RJ</City>

            </Address>

            <Address>

                <AddressType>Office</AddressType>

                <Address1>temp</Address1>

                <Address2>ppp road</Address2>

                <City>RJ</City>

            </Address>

        </AddressCollection>

    </Agent>

    <Agent AgentID="2">

        <Fname>Jacob</Fname>

        <SSN>321-52-4562</SSN>

        <AddressCollection>

            <Address>

                <AddressType>Home</AddressType>

                <Address1>xxx</Address1>

                <Address2>aaa road</Address2>

                <City>NY</City>

            </Address>

            <Address>

                <AddressType>Office</AddressType>

                <Address1>ccc</Address1>

                <Address2>oli Com</Address2>

                <City>CL</City>

            </Address>

            <Address>

                <AddressType>Temp</AddressType>

                <Address1>eee</Address1>

                <Address2>olkiu road</Address2>

                <City>CL</City>

            </Address>

        </AddressCollection>

    </Agent>

    <Agent AgentID="3">

        <Fname>Tom</Fname>

        <SSN>252-52-4563</SSN>

        <AddressCollection>

            <Address>

                <AddressType>Home</AddressType>

                <Address1>ttt</Address1>

                <Address2>loik road</Address2>

                <City>NY</City>

            </Address>

        </AddressCollection>

    </Agent>

</Agents>

*/

Passing a Data Table to a SQL Server 2005 Stored Procedure

One of the readers at SQLServerCentral forum asked me about passing a DataTable to a stored procedure. He wanted to get an XML representation of the DataTable and then pass it to the stored procedure. I tried to answer the question and came up with a complete sample code, which I think would be useful to others too.

Here is what the sample code does.

1. Opens a connection to a database
2. Executes a stored procedure which returns a DataSet with 2 DataTables
3. Get the XML out of the first table
4. Pass the XML to a stored procedure

Before we have a look at the code, we need to create a database and create a couple of tables. Then we need to populate the tables with some sample data. Here is the code.

-- Create a Database

CREATE DATABASE DataTableTest

USE DataTableTest

GO

--Create the sample tables

CREATE TABLE Employees (

    EmployeeID BIGINT IDENTITY(1,1),

    EmployeeName VARCHAR(50),

    DepartmentID BIGINT )

 

CREATE TABLE Departments (

    DepartmentID BIGINT IDENTITY(1,1),

    DepartmentName VARCHAR(50) )

 

GO

-- Populate the Sample Tables

INSERT INTO Departments ( DepartmentName)

SELECT 'Software'

 

INSERT INTO Employees (EmployeeName, DepartmentID )

SELECT 'Jacob', 1

GO

 

Now let us create a stored procedure which returns two result sets.

 

CREATE PROCEDURE GetEmployeeInfo

AS

SET NOCOUNT ON

 

SELECT EmployeeName, DepartmentID

FROM Employees

WHERE EmployeeID = 1

 

SELECT DepartmentName FROM Departments

WHERE DepartmentID = 1

GO

Let us create the next stored procedure which accepts an XML parameter. This procedure will insert the data from the XML parameter, into the Employee Table.

CREATE PROCEDURE ProcessXml

(

    @data XML

)

AS

 

INSERT INTO Employees(EmployeeName, DepartmentID)

SELECT

    x.d.value('EmployeeName[1]','VARCHAR(50)') AS EmployeeName,

    x.d.value('DepartmentID[1]','INT') AS DepartmentID

FROM @data.nodes('/NewDataSet/Table') x(d)

 

GO

 

Now Let us see the VB.NET code. I have created a VB.NET console application which performs the 4 steps mentioned above.

Note: The VB.NET code presented here may not be the best possible code. The intension of writing this code is to present the best code to perform the given operation. The idea is to present a basic code which WORKS!

 

Imports System.Data

Imports System.Data.SqlClient

Imports System.IO

Module Module1

 

    Sub Main()

        'Define a connection string

        Dim conStr As String

        conStr = "Data Source=TOSHIBA-USER\SQL2005;Initial Catalog=DataTableTest;Integrated Security=True"

 

        'Create and open a new connection

        Dim cn As New SqlConnection(conStr)

        cn.Open()

 

        'Create a command to retrieve data from SP

        Dim cmd As New SqlCommand("GetEmployeeInfo", cn)

        cmd.CommandType = CommandType.StoredProcedure

 

        'Fill the DataSet

        Dim da As New SqlDataAdapter(cmd)

        Dim ds As New DataSet()

        da.Fill(ds)

        da.Dispose()

        cmd.Dispose()

 

        'Access the first table

        Dim dt As DataTable

        dt = ds.Tables(0)

 

        'Create a stream object and Write the content of the DataTable

        'to it.

        Dim s As New MemoryStream()

        dt.WriteXml(s, True)

 

        'Retrieve the text from the stream

        s.Seek(0, SeekOrigin.Begin)

        Dim sr As New StreamReader(s)

        Dim xmlString As String

        xmlString = sr.ReadToEnd()

 

        'close

        sr.Close()

        sr.Dispose()

 

        'pass the XML data to sqlserver

        cmd = New SqlCommand("ProcessXml", cn)

        cmd.CommandType = CommandType.StoredProcedure

        Dim p As SqlParameter

        p = cmd.Parameters.AddWithValue("@data", xmlString)

        p.SqlDbType = SqlDbType.Xml

        cmd.ExecuteNonQuery()

        cmd.Dispose()

 

        'Close connection

        cn.Close()

        cn.Dispose()

    End Sub

 

End Module

Through different web development courses, one learns the importance of a web host that provides linux hosting as well as backup software. The courses also emphasize upon need of wireless internet providers and search engine services.