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
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 Facebookpage 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
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
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
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.
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.
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.
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:
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.
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.
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!
I have re-written the same function with function STUFF, and it removes any limit imposed on the script.
-- Check Table Column
-- Get CSV values
(SELECT ',' + s.Name
FROM HumanResources.Shift s
ORDER BY s.Name
FOR XML PATH('')),1,1,'') AS CSV
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.
For the purpose of this example, let us create an XML Schema Collection to validate the following XML Document.
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.
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.
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
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.
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.
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.
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.
SELECT a.b.value(‘Colors/Color1’,‘varchar(10)’) AS Color1, a.b.value(‘Colors/Color2’,‘varchar(10)’) AS Color2, a.b.value(‘Colors/Color3’,‘varchar(10)’) AS Color3, a.b.value(‘Colors/Color4/@Special’,‘varchar(10)’)+‘ ‘+
+a.b.value(‘Colors/Color4’,‘varchar(10)’) AS Color4, a.b.value(‘Colors/Color5’,‘varchar(10)’) AS Color5, a.b.value(‘Fruits/Fruits1’,‘varchar(10)’) AS Fruits1, a.b.value(‘Fruits/Fruits2’,‘varchar(10)’) AS Fruits2, a.b.value(‘Fruits/Fruits3’,‘varchar(10)’) AS Fruits3, a.b.value(‘Fruits/Fruits4’,‘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.