SQL SERVER – Group by Rows and Columns using XML PATH – Efficient Concating Trick

I hardly get hard time to come up with the title of the blog post. This was one of the blog post even though simple, I believe I have not come up with appropriate title. Any way here is the question I received.

“I have a table of students and the courses they are enrolled with the name of the professor besides it. I would like to group the result with course and instructor name.

For example here is my table:

How can I generate result as following?

Now you can see how easy the question is but so hard to come up with either solution or  title of this blog post. We can use XML PATH and come up with the solution where we combine two or more columns together and display desired result.

Here is the quick script which does the task ask, I have used temporary tables so you can just take this script and quickly run on your machine and see how it returns results.

Let me know if there are any better ways to do the same.

-- Create table
CREATE TABLE #TestTable (StudentName VARCHAR(100), Course VARCHAR(100), Instructor VARCHAR(100), RoomNo VARCHAR(100))
GO
-- Populate table
INSERT INTO #TestTable (StudentName, Course, Instructor, RoomNo)
SELECT 'Mark', 'Algebra', 'Dr. James', '101'
UNION ALL
SELECT 'Mark', 'Maths', 'Dr. Jones', '201'
UNION ALL
SELECT 'Joe', 'Algebra', 'Dr. James', '101'
UNION ALL
SELECT 'Joe', 'Science', 'Dr. Ross', '301'
UNION ALL
SELECT 'Joe', 'Geography', 'Dr. Lisa', '401'
UNION ALL
SELECT 'Jenny', 'Algebra', 'Dr. James', '101'
GO
-- Check orginal data
SELECT *
FROM #TestTable
GO
-- Group by Data using column and XML PATH
SELECT
StudentName,
STUFF((
SELECT ', ' + Course + ' by ' + CAST(Instructor AS VARCHAR(MAX)) + ' in Room No ' + CAST(RoomNo AS VARCHAR(MAX))
FROM #TestTable
WHERE (StudentName = StudentCourses.StudentName)
FOR XML PATH (''))
,
1,2,'') AS NameValues
FROM #TestTable StudentCourses
GROUP BY StudentName
GO
-- Clean up
DROP TABLE #TestTable
GO

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Grouping by Multiple Columns to Single Column as A String

One of the most common questions I receive in email is how to group multiple column data in comma separate values in a single row grouping by another column.

I have previously blogged about it in following two blog posts. However, both aren’t addressing the following exact problem.

The question comes in many different formats but in following image I am demonstrating the same question in simple words. This is the most popular question on my Facebook page as well. (Example)

Here is the sample script to build the sample dataset.

CREATE TABLE TestTable (ID INT, Col VARCHAR(4))
GO
INSERT INTO TestTable (ID, Col)
SELECT 1, 'A'
UNION ALL
SELECT 1, 'B'
UNION ALL
SELECT 1, 'C'
UNION ALL
SELECT 2, 'A'
UNION ALL
SELECT 2, 'B'
UNION ALL
SELECT 2, 'C'
UNION ALL
SELECT 2, 'D'
UNION ALL
SELECT 2, 'E'
GO
SELECT *
FROM TestTable
GO

Here is the solution which will build an answer to the above question.

-- Get CSV values
SELECT t.ID, STUFF(
(
SELECT ',' + s.Col
FROM TestTable s
WHERE s.ID = t.ID
FOR XML PATH('')),1,1,'') AS CSV
FROM TestTable AS t
GROUP BY t.ID
GO

I hope this is an easy solution. I am going to point to this blog post in the future for all the similar questions.

Final Clean Up Act
-- Clean up
DROP TABLE TestTable
GO

Here is the question back to you –
Is there any better way to write above script? Please leave a comment and I will write a separate blog post with due credit.

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – View XML Query Plans in SSMS as Graphical Execution Plan

Earlier I wrote a blog post on SQL SERVER – Parallelism – Row per Processor – Row per Thread, where I mentioned the XML Plan. As a follow up on the blog post, I received the request to send the same execution plan so that the blog readers can also use the same and reproduce it on their machine. I realized that I have actually never written on how one can send a graphical execution plan to another user so that they can reproduce the same exact details without all the actual tables, indexes and objects.

Here is very simple method on how one can do that.

Right Click on Execution Plan and click on “Save Execution Plan As…”.

You can save the plan with the extension .sqlplan. The same plan can be sent to another user via email or a USB drive. Another user can just double click on the file and open the execution plan at another local computer without physically having any underlying object.

This is very simple trick; you can also send the execution plan in text format as well. We will talk about it in some other post.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – Guest Post – SELECT * FROM XML – Jacob Sebastian

One of the most common problem SQL Server developers face while dealing with XML is related to writing the correct XPath expression to read a specific value from an XML document. I usually get a lot of questions by email, on my blog or in the forums which looks like the following:

“I have the following XML document and I am trying to read the value from xyz node. When I run my query, I get a NULL value”

My friend Jacob Sebastian (SQL Server MVP) has written excellent article on the subject SELECT * FROM XML, I strongly recommend to either bookmark it and read it before continuing further in this article.

In most cases, I have seen that the problem was due to an incorrect XPath expression. XPath expressions are not complicated at all, but they need a close attention to get them right. Assume that we have the following XML fragment.

Looking at the structure of the XML document, it is quite easy to figure out the XPath expression pointing to each element and attribute. The following illustration shows how to do this.

One of the simplest ways is to identify the nodes with their position as given in the above illustration. A more detailed listing is given below.

The above example also used the “position” of the elements to uniquely identify them. In real life you might need to have more complex matching criteria such as the “email of the Employee element whose id is 10001”. The following example shows how to apply this type of filters.

/Employees/Employee[@id=”1001″]/Email

In most cases, you will be able to easily build your XPath expressions. However, if you find it difficult, you can take help from my helper function given here. This function allows you to run ‘blind’ queries on the XML document very similar to the ‘select * from table’ queries that we usually run on unknown tables.

For example, if you would like to quickly examine the above XML document and see the elements, attributes and their XPath expression, you can execute something like the following:

Make sure that you create the function XMLTable() using the script given in the above URL.

Once you have the output of the function, you can copy the XPath expressions from the results and use in your Queries. For example, if you are looking for the email address of Pinal, you can just copy the expression from row 8 (highlighted in the image given above) and use in your query as:

SELECT @x.value(‘Employees[1]/Employee[2]/Email[1]’,’VARCHAR(50)’)

I hope you will find this post interesting and the XMLTable() function might help you to solve some of the XML querying problems you may face in your SQL Server Journey. If you have got any question about XML in general, or about this function in particular, please feel free to post them on the XML forum and I will try my best to help you out.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – Converting a Delimited String of Values into Columns

This blog post is about two great bloggers and their excellent series of blog posts. It was quite unusual to see two bloggers posting articles that are supporting each other and constantly improving the articles to the next level.

Two blogs which I am going to mention here are as follows: SELECT Blog FROM Brad.Schulz CROSS APPLY SQL.Server() – Brad Schulz and Demystifying SQL Server – Adam Haines. Before continuing this blog post, I suggest you all to bookmark these blogs for future reference.

The whole thing started when Adam tried to answer the question “How to transform a delimited values into columns?” on MSDN SQL Forum. Adam made the first blog post here http://jahaines.blogspot.com/2009/06/converting-delimited-string-of-values.html and then Brad and Adam bounced a few ideas off the wall. Then, Adam started his series on concatenating column values http://jahaines.blogspot.com/2009/06/concatenating-column-values-part-1.html, and http://jahaines.blogspot.com/2009/07/concatenating-column-values-part-2.html.  In part two of the series, Brad wanted to get a deeper understanding of why the results came out the way they did. Adam then started another series on unpacking or parsing out delimited characters, http://jahaines.blogspot.com/2009/11/splitting-delimited-string-part-1.html and here http://jahaines.blogspot.com/2009/12/splitting-delimited-string-part-2.html.  From here, Brad started digging even deeper into the internals of the XML method. Brad has a comprehensive list of the back and forth on this post, http://bradsruminations.blogspot.com/2009/12/delimited-string-tennis-anyone.html and a final post here http://bradsruminations.blogspot.com/2010/01/delimited-string-tennis-again-final.html.

The final outcome is that both the XML method and number table can perform equally, but the XML method has to be coded in a very particular way; otherwise performance will be horrendous. Also, the permanent number table shows consistently more performance than the inline number table; however, on smaller string sizes, the difference here is that most of the inline number tables require more CPU and RAM, whereas the permanent number table requires more IO. For an average developer, the number table solution is probably the easiest of the solutions to implement.

Overall, I strongly suggest to go through the abovementioned posts; you will love it and become their fan. Please note these posts are very easy to understand and if you know a bit of XML only, you will still be able to understand them very well. The above description has been taken with proper consent of the Adam and Brad.

Hats Off to you Guys! You guys inspire me and many SQL enthusiasts!

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Comma Separated Values (CSV) from Table Column – Part 2

In my earlier post, I wrote about how one can use XML to convert table to string SQL SERVER – Comma Separated Values (CSV) from Table Column. The same article is also published on channel 9 SQLAuthority News – Featured on Channel 9. One of the very interesting points that was discussed on show was about the usage of function SUBSTRING. I found the following point very valid: SUBSTRING usage limits the length of the XML to be used.

I have re-written the same function with function STUFF, and it removes any limit imposed on the script.

USE AdventureWorks
GO
-- Check Table Column
SELECT [Name]
FROM HumanResources.Shift
GO
-- Get CSV values
SELECT STUFF(
(SELECT ',' + s.Name
FROM HumanResources.Shift s
ORDER BY s.Name
FOR XML PATH('')),1,1,'') AS CSV
GO

Do let me know your thoughts on the same.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Validate an XML document in TSQL using XSD by Jacob Sebastian

Following article is sent by SQL Server MVP Jacob Sebastian on request to provide solution for validating XML document.

XML Schema Collections

Because of the ‘eXtensible’ nature of XML (eXtensible Markup Language), often there is a requirement to restrict and validate the content of an XML document to a pre-defined structure and values. XSD (XML Schema Definition Language) is the W3C recommended language for describing and validating XML documents. SQL Server implements XSD as XML Schema Collections.

An XML Schema Collection can be used to validate an XML document stored in an XML column or variable.  To understand this better, let us see an example.

XML Document

For the purpose of this example, let us create an XML Schema Collection to validate the following XML Document.

<Employee>
    <FirstName>Jacob</FirstName>
    <MiddleName>V</MiddleName>
    <LastName>Sebastian</LastName>
</Employee>

Validation Rules

Here are the validation rules we are trying to implement.

  1. The root element should be named ‘Employee’
  2. The root element should have three child elements, named ‘FirstName’, ‘MiddleName’ and ‘LastName’.
  3. Child elements should appear exactly in the order given above.

Creating the XML Schema Collection

Here is the XML Schema collection that performs the above validations.

CREATE XML SCHEMA COLLECTION EmployeeSchema
    AS'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <xsd:element >
        <xsd:complexType>
            <xsd:sequence>
                <xsd:element />
                <xsd:element />
                <xsd:element />
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>'

Validating an XML variable

The following code validates an XML document against the rules defined in the XML Schema Collection.

DECLARE @x XML(EmployeeSchema)
SELECT @x = '<Employee>
    <FirstName>Jacob</FirstName>
    <MiddleName>V</MiddleName>
    <LastName>Sebastian</LastName>
</Employee>'

Note that the operation will succeed only if all the validation rules defined in the XML schema collection are met. For example, the following will fail because the XML document is missing the ‘MiddleName’ element.

DECLARE @x XML(EmployeeSchema)
SELECT @x = '<Employee>
    <FirstName>Jacob</FirstName>
    <LastName>Sebastian</LastName>
</Employee>'

Please read further details on the same subject over SyntaxHelp XML.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Comma Separated Values (CSV) from Table Column

I use following script very often and I realized that I have never shared this script on this blog before. Creating Comma Separated Values (CSV) from Table Column is a very common task, and we all do this many times a day. Let us see the example that I use frequently and its output.

USE AdventureWorks
GO
-- Check Table Column
SELECT Name
FROM HumanResources.Shift
GO
-- Get CSV values
SELECT SUBSTRING(
(
SELECT ',' + s.Name
FROM HumanResources.Shift s
ORDER BY s.Name
FOR XML PATH('')),2,200000) AS CSV
GO

I consider XML as the best solution in terms of code and performance. Further, as I totally prefer this option, I am not even including the linka to my other articles, where I have described other options.


Do you use any other method to resolve this issue? Can you find any significant difference in performance between these options? Please leave your comment here.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Practical SQL Server XML: Part One – Query Plan Cache and Cost of Operations in the Cache

I am very fortunate that I have friends like Michael Coles. Michael Coles is SQL Server and XML expert and have written many books on SQL Server as well XML. He has previously written book which I have reviewed on this blog SQLAuthority News – Book Review – Pro T-SQL 2005 Programmer’s Guide (Paperback). I am currently reading his latest book Pro SQL Server 2008 XML (Hardcover) which can be found on amazon. I will be writing review of the book once I am done reading it.

Michael Coles and I met last at Microsoft MVP Summit 2009 at Seattle and I come to know he very much young and dynamic person who is friend of everybody. His knowledge of SQL Server is exception and I had requested him to write one practical demonstration of XML and he took up this challenge very well and the out come of that challenge is I am going to publish in this blog as it is.

Michael Coles and Pinal Dave

I spoke to Pinal Dave at the MVP Summit this week, and he challenged me to provide his readers with a practical demonstration of using XML in the database.  I took up the challenge, and decided to go one better by giving two practical demonstrations.

This post is the first demonstration, which queries the SQL Server query plan cache and returns the cost of operations in the cache.  The query simply shreds the cached XML query plans and grabs all the RelOp nodes, which represent all the operators used by the query plan. In the second post at http://sqlblog.com/blogs/michael_coles/archive/2009/03/03/practical-sql-server-xml-part-2.aspx I’ll demonstrate passing XML parameter “sets” into a stored procedure.  As a bonus, SQL MVP Jacob Sebastian has agreed to add a third post on his blog, at http://blog.beyondrelational.com/2009/03/practical-sql-server-xml-part-three.html that will give a practical example of using XML Schema in SQL Server.

The XML Query plans look like this in the cache (note that I simplified the XML and this is only a small portion that’s relevant):

<QueryPlan xmlns:p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"
CachedPlanSize="112"
CompileTime="500"
CompileCPU="53"
CompileMemory="1968">

<RelOp NodeId=“0”
PhysicalOp=“Sequence Project”
LogicalOp=“Compute Scalar”
EstimateRows=“227.951”
EstimateIO=“0”
EstimateCPU=“1.82361e-005″
AvgRowSize=“5202”
EstimatedTotalSubtreeCost=“930.972” >

<RelOp NodeId=“1”
PhysicalOp=“Segment”
LogicalOp=“Segment”
EstimateRows=“227.951”
EstimateIO=“0”
EstimateCPU=“4.55901e-006″
AvgRowSize=“5202”
EstimatedTotalSubtreeCost=“930.972” >

</RelOp>

</RelOp>
</
QueryPlan>

The equivalent graphical execution plan looks like this (note this is a partial screenshot):

(Click on image to see enlarged image)

(Note that you can save your graphical execution query plans as XML also – just right-click in the graphical execution query plan window and save it as a .sqlplan file. The .sqlplan file is in XML format – you can verify this by opening it in NotePad).

Since SQL Server 2005 and 2008 store the query plans in the cache as XML we can get all of the same information in relational format.  The sample query below returns attributes of each operator in each query plan including physical and logical operators, estimated cost, estimated IO, estimated CPU cost, and an estimated number of rows returned.

WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
CachedPlans
(
ParentOperationID,
OperationID,
PhysicalOperator,
LogicalOperator,
EstimatedCost,
EstimatedIO,
EstimatedCPU,
EstimatedRows,
PlanHandle,
QueryText,
QueryPlan,
CacheObjectType,
ObjectType)
AS
(
SELECT
RelOp.op.value(N'../../@NodeId', N'int') AS ParentOperationID,
RelOp.op.value(N'@NodeId', N'int') AS OperationID,
RelOp.op.value(N'@PhysicalOp', N'varchar(50)') AS PhysicalOperator,
RelOp.op.value(N'@LogicalOp', N'varchar(50)') AS LogicalOperator,
RelOp.op.value(N'@EstimatedTotalSubtreeCost ', N'float') AS EstimatedCost,
RelOp.op.value(N'@EstimateIO', N'float') AS EstimatedIO,
RelOp.op.value(N'@EstimateCPU', N'float') AS EstimatedCPU,
RelOp.op.value(N'@EstimateRows', N'float') AS EstimatedRows,
cp.plan_handle AS PlanHandle,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan,
cp.cacheobjtype AS CacheObjectType,
cp.objtype AS ObjectType
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY qp.query_plan.nodes(N'//RelOp') RelOp (op)
)
SELECT
PlanHandle,
ParentOperationID,
OperationID,
PhysicalOperator,
LogicalOperator,
QueryText,
CacheObjectType,
ObjectType,
EstimatedCost,
EstimatedIO,
EstimatedCPU,
EstimatedRows
FROM CachedPlans
WHERE CacheObjectType = N'Compiled Plan';

The results look similar to the following:

(Click on image to see enlarged image)

Since the XML query plan is hierarchical in structure, with operations nested within other operations, I’ve added the columns ParentOperationID and OperationID.  The ParentOperationID values point to the current operator’s parent operator.  I added the arrows in the results above to demonstrate.

So what’s the practical use of this?  Well, once you have the XML query plan information in relational format it’s relatively easy to manipulate.  You can determine, for instance, the Top 10 most costly queries in your cache and the operators in those queries are the most expensive. You can even log this information to a table or send it out in other formats.

In part 2 of this series I’ll talk about another practical use for SQL Server-based XML, at http://sqlblog.com/blogs/michael_coles/archive/2009/03/03/practical-sql-server-xml-part-2.aspx.  SQL Server MVP Jacob Sebastian will finish the series with part 3 on his blog at http://blog.beyondrelational.com/2009/03/practical-sql-server-xml-part-three.html.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Simple Example of Reading XML File Using T-SQL

In one of the previous article we have seen how we can create XML file using SELECT statement SQL SERVER – Simple Example of Creating XML File Using T-SQL. Today we will see how we can read the XML file using the SELECT statement.

Following is the XML which we will read using T-SQL:

Following is the T-SQL script which we will be used to read the XML:

DECLARE @MyXML XML
SET @MyXML = '<SampleXML>
<Colors>
<Color1>White</Color1>
<Color2>Blue</Color2>
<Color3>Black</Color3>
<Color4 Special="Light">Green</Color4>
<Color5>Red</Color5>
</Colors>
<Fruits>
<Fruits1>Apple</Fruits1>
<Fruits2>Pineapple</Fruits2>
<Fruits3>Grapes</Fruits3>
<Fruits4>Melon</Fruits4>
</Fruits>
</SampleXML>'

SELECT
a.b.value(‘Colors[1]/Color1[1]’,‘varchar(10)’) AS Color1,
a.b.value(‘Colors[1]/Color2[1]’,‘varchar(10)’) AS Color2,
a.b.value(‘Colors[1]/Color3[1]’,‘varchar(10)’) AS Color3,
a.b.value(‘Colors[1]/Color4[1]/@Special’,‘varchar(10)’)+‘ ‘+
+
a.b.value(‘Colors[1]/Color4[1]’,‘varchar(10)’) AS Color4,
a.b.value(‘Colors[1]/Color5[1]’,‘varchar(10)’) AS Color5,
a.b.value(‘Fruits[1]/Fruits1[1]’,‘varchar(10)’) AS Fruits1,
a.b.value(‘Fruits[1]/Fruits2[1]’,‘varchar(10)’) AS Fruits2,
a.b.value(‘Fruits[1]/Fruits3[1]’,‘varchar(10)’) AS Fruits3,
a.b.value(‘Fruits[1]/Fruits4[1]’,‘varchar(10)’) AS Fruits4
FROM @MyXML.nodes(‘SampleXML’) a(b)

Please note in above T-SQL statement XML attributes is read the same as XML Value.

Reference : Pinal Dave (http://blog.SQLAuthority.com)