September, 2008 | Michael J. Swart

September 29, 2008

Passing XML Data from the App to the Database

Filed under: Technical Articles — Tags: "sql server 2005", "sql server 2008", "sql server", nodes, shredding, shredding xml, xml, xml shredding — Michael J. Swart @ 5:27 pm

Passing xml data from the app to the database

So I recently talked about how SQL Server can take xml data and shred it into something more relational.

I wanted to follow it up with this post and show an example of how to use ADO.Net to make use of SQL’s xml data type and pass it to an application.

First say that you have a stored procedure with this signature:

CREATE PROCEDURE ShredThis(@p1 XML)
AS

Then you can use that procedure from within C# code with something like this:

private void button1_Click(object sender, EventArgs e)
{
    SqlCommand cmd = null;
    try
    {
        cmd = new SqlCommand();
        cmd.CommandText = "ShredThis";
        cmd.CommandType = CommandType.StoredProcedure;
        XmlReader xr = XmlReader.Create(new StringReader(@"
        <book author=""J K Rowling"" Title=""Harry Potter"">
            <chapter number =""1"" name=""the boy who lived""><chapter>
            <chapter number =""2"" name=""diagon alley""><chapter>
            <chapter number =""3"" name=""quidditch""><chapter>
            <chapter number =""4"" name=""dementors""><chapter>
            <chapter number =""5"" name=""voldemort's back""><chapter>
        <book>"));
        cmd.Parameters.Add("@p1", SqlDbType.Xml).Value = new SqlXml(xr);
        cmd.Connection = new SqlConnection(@"Data Source=mswart\SQL2008;Initial Catalog=tempdb;Trusted_Connection=True");
        cmd.Connection.Open();
        cmd.ExecuteNonQuery();
    }
    finally
    {
        if (cmd != null && cmd.Connection != null && (cmd.Connection.State == ConnectionState.Open))
        {
            cmd.Connection.Close();
        }
    }
}

You can cut and paste this into any app and adjust it to how you need it. Remember a couple things:

Don’t forget the using clauses to make sure the code compiles
Check out the SqlXml class.
The SqlDbType enumeration has an Xml entry. This is what it’s used for

Cheers,

Michael J. Swart

-- Comments (0)

A better XML shredding example

Filed under: SQL Scripts,Technical Articles — Tags: "sql server 2005", "sql server 2008", "sql server", nodes, shredding, shredding xml, xml, xml shredding — Michael J. Swart @ 12:41 pm

Passing data into the database as a set has always been a challenge. There have been a number of approaches used for various purposes. And those approaches are discussed in many different places already.

If you only have to support SQL 2008, then table valued parameters are definitely the way to go.

If you have to support SQL 2005 (like myself) then other methods have to be used. Such as by parsing CSVs. Or my new favorite method of shredding xml.

Shredding XML
I like this method because it maintains data/script separation which is important from a security point of view.

One way of shredding xml is by using the nodes() method of the xml data type. The official documentation is here, but it wasn’t clear how to use this method for the business case I mentioned.

Here’s my example which I use as a template. Maybe you’ll find it useful too:

DECLARE @data XML
SET @data = '<root xmlns="http: //www.MySampleCompany.com">
 <book author="J K Rowling" title="Philosopher''s Stone">
  <chapter number="1" name="the boy who lived"/>
  <chapter number="2" name="the rest"/>
 </book>
</root>';

WITH XMLNAMESPACES ('http: //www.MySampleCompany.com' AS MY) 
SELECT 
   chapters.node.value('../@title', 'nvarchar(50)') AS bookTitle,
   chapters.node.value('../@author', 'nvarchar(50)') AS bookAuthor,
   chapters.node.value('@number', 'int') AS chapterNumber,
   chapters.node.value('@name', 'nvarchar(50)') AS chapterName
FROM @data.nodes('//MY:chapter') AS chapters(node)

The results look like this

bookTitle             bookAuthor  chapterNumber chapterName 
--------------------- ----------- ------------- ------------------ 
Philosopher's Stone   J K Rowling 1             the boy who lived 
Philosopher's Stone   J K Rowling 2             the rest

Also, if you don’t use namespaces with your XML, you just omit the WITH clause.

Update!
Check out this post to see how this feature looks from the app side (using c#).

-- Comments (2)

September 10, 2008

Self replicating SQL

Filed under: Tongue In Cheek — Michael J. Swart @ 12:22 pm

While thinking about self replicating programs (See Quine). It became natural to think about self replicating T-SQL, and not just that, but the shortest self-replicating SQL out there.

And naturally, some-one beat me too it. (Ken Henderson wrote about it here.)

Ken mentioned that there seemed to be two flavors of submissions:

Those that tried to cheat by accessing a cached copy of the source code somewhere external to the code (e.g., from syscomments) and those that genuinely tried to replicate themselves

Now I wouldn’t call it cheating, but in the end, it’s his blog. Here’s what I came up with myself along those lines:

SELECT TEXT 
FROM sys.dm_exec_cached_plans p 
CROSS apply sys.dm_exec_sql_text(p.plan_handle) s 
WHERE TEXT LIKE '%|%'

and

DBCC inputbuffer(@@spid)

Although this last example doesn’t quite work out.

There are two more solutions that I like that don’t fall into either of Ken’s categories. One was actually mentioned in the comments of his post, and that’s the null batch. Executing nothing will print nothing. This was submitted by Itzik Ben Gan (not a surprise). This is clearly the winner when it comes to shortest self-replicating T-SQL script.

And then there’s this one I came up with. It seems like an abuse of the rules, but that’s why I like it.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '15'.

-- Comments (0)

September 3, 2008

When pasting from SQL Profiler, doesn't reproduce the problem.

Filed under: Technical Articles — Michael J. Swart @ 7:21 am

So I got a problem referred to me yesterday where a colleague wrote:

This [query] seemed quite odd to me at first… I ran SQL profiler to catch the exact sproc that was being called when the timeout occurred and ran it manually on the DB… it produced no results and ran in under 5s…

Sure enough I also had trouble reproducing the bad performance. It turns out this sproc was building a query dynamically and running it. Somehow it selected different plans based on the context of an app, or the context of a query window.

But I finally figured out how to call the sproc from a query window with the plan taken from the application. Long story short, I forced the plan on the query as described here

Short story long:

First step is to find the sql handle and the plan handle being used by the application (add whatever filters you need):

SELECT TOP 10 st.TEXT, er.sql_handle, er.plan_handle
FROM sys.dm_exec_requests er
CROSS apply sys.dm_exec_sql_text st(er.sql_handle)
ORDER BY er.start_time ASC

Then paste the following query in to your query window. This is the syntax for the USE PLAN option.

<query text> 
OPTION (USE PLAN N' 
<query plan>
')

Replace the <query text> above with the text that you got from the first query above. Replace the <query plan> with the plan you get from the sys.dm_exec_query_plan() function. Don’t forget to change each single quote into two single quotes.

What does this help you do?
It helps reproduce problems so that you can dig deeper into the execution of a query. This is mostly useful when the query plan isn’t enough. You might want the query plan as well as the performance statistics. Or maybe you want to take this query and run it on a different dev machine. In my case, it helped me track down which cost estimates in the plan were out of whack and ultimately which statistics were out of date.

-- Comments (0)