OpenXML – all-in-one processing data

Introduction

The XML standard  is an intense used standard, because confers possibility to “carry” in a easy and fully understandable way information between different entities, belonging to different application on contained in same application. Capability of an XML “message” to be configurable and processed, due of its “architecture” recommends it as an ideal “transporter” to communicate with outside world, receiving and sending information. As the time passed, more and more technologies started to use XML standard, while the new ones (such as BizTalk) made from XML technology a “cornerstone”, relying their entire philosophy on XML.

 

XML in SQL Server usage

                Obviously, a very powerful technology, such SQL Server, which is one of the most used RDBMS in the world, could not avoid using the XML standard. Among XML standard utilization, we distinguish 3 of most important SQL mechanisms based on XML technology.

1.     Receive XML data

2.     Return XML data

3.     Storing XML data

Receive XML data

Through this mechanism, we can receive XML documents from another applications and use further within SQL statements in order to insert/update/delete/select data. This is a very useful feature in some cases, such as:

-       SQL statements (stored procedure, function) are called by applications which use XML as basic model of representing data (for example, BizTalk Server). In this case, is difficult to process the message before to send it to SQL statement in order to send just individual values, but is easier to send entire XML message to SQL statement and to process it there.

-       In case of a bulk insert/update/delete operations from applications which call SQL statements. In this case, is inappropriate to get component fields of every row to be inserted, and send them to SQL statement, because, for each row, we must open a connection to SQL Server, execute the statement with these fields as parameters, and close connection. For a bigger number of this kind of calls, it is obviously that performance of application and SQL server also will be affected. Instead, we can send entire batch as an XML document, and perform the insert/update/delete operations from within SQL statement (usually, a stored procedure). Of course, in case of multiple deleting operation, we can construct a document which contains only the elements constituting the primary key of table(s) from within rows will be deleted, and perform the deletion for that records.

Example

 

Let’s discuss on following scenario:

-       An ASP.NET application – Visual C# 2005, framework 2.0 – we will call it Company

-       In Company application, we will have a page, Customers.aspx, where, when we will press a button, we will receive a dataset with customers, from a data provider (imported from another database or file, received from a web service etc.). In our case, will be an XML file stored in application.

-       After we will receive the dataset with customers, we will insert in our database using openxml function.

To import the customers from file Customers.xml from directory solution to database, the steps are:

1.     On a new or existing database, run script CustomerTable.sql from SQlFiles folder, to add table TblCustomer

2.     On the same database run the script usp_InsertCustomers.sql from same folder, to add stored procedure usp_InsertCustomers

3.     Install application Companies on IIS server and open it with URL http://localhost/Companies.aspx

4.     Click on button ‘Import customers’. If the import goes correctly, will appear the message “Customers were inserted”

5.     Check in table TblCustomer, to see the customers imported

What happened in the back? There are some operations which occurs when button ‘Import customers’ is pressed. The operations are:

1.     The content of the Customers.xml file is loaded in a DataSet object. When xml content is loaded, DataSet object identifies automatically the tables existing in xml structure, creates them, am populates them with data from xml. Normally, we should use a xml schema to check if xml content matches an expected structure and, thus, to avoid error. But, in this example, to simplify the model, we will consider a valid structure coming from xml file.

For this, we have the function

private DataSet LoadCustomersData()

{

DataSet dsCustomers = new DataSet("Customers");

dsCustomers.ReadXml(Server.MapPath("Customers.xml"));

return dsCustomers;

}

2.     The DataSet object is outputted to a string, which is passed to a DAL function, to be inserted in database

 

protected void ImportCustomers_Click(object sender, EventArgs e)

{

DataSet dsCustomers = LoadCustomersData();

StringWriter swCustomers = new StringWriter();

dsCustomers.WriteXml(swCustomers, XmlWriteMode.IgnoreSchema);

InsertCustomers(swCustomers.ToString());

}

private void InsertCustomers(string xmlCustomers)

{

lblResult.Text = CompaniesDAL.Customers.InsertCustomers(xmlCustomers);

      }

Of course, we can pass the xml file content to a DAL method directly, without using DataSet object. Here, we consider the possibility to receive this xml content to a various data sources, and in some cases, using a DataSet object may be useful, so, we used Dataset object.

3.     DAL method passes the xml string to a stored procedure, as a parameter, in order to be inserted in table TblCustomer.

public static string InsertCustomers(string xmlCustomers)

{

string strConnectionConfigurationManager.

ConnectionStrings["TestSQLXML"].

ToString();

SqlConnection sqlConnection = new SqlConnection(strConnection);

 

SqlCommand sqlCommand = new SqlCommand();

sqlCommand.Connection = sqlConnection;

sqlCommand.CommandText = "usp_InsertCustomers";

sqlCommand.CommandType = CommandType.StoredProcedure;

//pass xml string as parameter

SqlParameter sqlParam = new SqlParameter("@xmlCustomers", SqlDbType.VarChar, 8000);

sqlParam.Value = xmlCustomers;

sqlCommand.Parameters.Add(sqlParam);

 

try

{

sqlConnection.Open();

sqlCommand.ExecuteNonQuery();

}

catch (Exception ex)

{

return ex.Message;

}

finally {

if(sqlConnection.State == ConnectionState.Open)

sqlConnection.Close();

}

return "Customers were inserted";

}

 

4.     Inserting the xml in table TblCustomers using procedure usp_InsertCustomers. Let’s take a closer look to this procedure

 

create procedure [dbo].[usp_InsertCustomers]

@xmlCustomers varchar(8000)

as

begin

declare @i int

exec sp_xml_preparedocument @i output, @xmlCustomers

insert into dbo.TblCustomer

select * from

openxml(@i, '/Customers/Customer', 2)

with

(

CustomerType int,

CustomerFirstName varchar(50),

CustomerLastName varchar(50),

CustomerBirthDate datetime

)

exec sp_xml_removedocument @i

end

                We can observe how the procedure makes the job:

-       It receives the xml content as a string (@xmlCustomers varchar(8000))

-       Uses the procedure sp_xml_preparedocument to create an internal representation of xml document based on string received. The procedure returns a handle, stored in variable @i, to identity the document in next operations

-       Next, is performed a SELECT operation over xml document

o     openxml procedure get the parameters:

§  @i – specifies which document must be processed, since @i handles the xml document associated with it by the procedure sp_xml_preparedocument called earlier

§  it is specified the level from where the selection must be done ('/Customers/Customer')

§  last parameter Indicates the mapping that should be used between the XML data and the relational rowset, and how the spill-over column should be filled. Possible values are:

·         0 Defaults to attribute-centric mapping.

·         1 Use the attribute-centric mapping. Can be combined with XML_ELEMENTS; in which case, attribute-centric mapping is applied first, and then element-centric mapping is applied for all columns not yet dealt with.

·         2 Use the element-centric mapping. Can be combined with XML_ATTRIBUTES; in which case, attribute-centric mapping is applied first, and then element-centric mapping is applied for all columns not yet dealt with.

·         8 Can be combined (logical OR) with XML_ATTRIBUTES or XML_ELEMENTS.
In context of retrieval, this flag indicates that the consumed data should not be copied to the overflow property @mp:xmltext.

In our example, this parameter will take value 2, because our xml document is element-centric.

o    with clause specifies the structure of dataset that will be filled by select operation. Thus, we define columns that come from xml document:

§  CustomerType int

§  CustomerFirstName varchar(50)

§  CustomerLastName varchar(50)

§  CustomerBirthDate datetime

The columns must match with columns from TblCustomer, to be able to perform insert operation

-       Rows returned by select statement will be inserted in TblCustomer table.

insert into dbo.TblCustomer

select * from openxml...

-       After insert, we must release resources involved in these operations

exec sp_xml_removedocument @i – free memory occupied by xml document, associated with @i handle

Using this mechanism, we performed a SQL operation over multiple records. In this way, we reduce the number of operations of opening/closing SQL connections, increasing the performance. More than that, passing the XML document on the SQL server and processing there gives us the possibility to put all processing in one place, making developer’s life easier.

               

OpenXML.rar (31.76 kb)