Archive

Archive for July, 2011

SELECT or Query nodes in hierarchial or nested XML

July 13, 2011 22 comments

On this post I’ll show how to retrieve information from nested XML.

I’ve shown this with an example with 2 approaches/methods and it’s up to you what to chose based upon their performance. Here we go:

DECLARE @xml XML

SET @xml=N'<Root>
	<orderHRD ID="101">
		<custID>501</custID>
		<orderDTL ID="201">
			<prodID>1</prodID>
			<qty>5</qty>
			<cost>25.12</cost>
		</orderDTL>
		<orderDTL ID="202">
			<prodID>2</prodID>
			<qty>3</qty>
			<cost>30.00</cost>
		</orderDTL>
	</orderHRD>
	<orderHRD ID="102">
		<custID>502</custID>
		<orderDTL ID="203">
			<prodID>11</prodID>
			<qty>12</qty>
			<cost>140.78</cost>
		</orderDTL>
	</orderHRD>
	<orderHRD ID="103">
		<custID>503</custID>
		<orderDTL ID="204">
			<prodID>6</prodID>
			<qty>8</qty>
			<cost>60.35</cost>
		</orderDTL>
		<orderDTL ID="205">
			<prodID>9</prodID>
			<qty>2</qty>
			<cost>10.50</cost>
		</orderDTL>
		<orderDTL ID="206">
			<prodID>10</prodID>
			<qty>6</qty>
			<cost>120.89</cost>
		</orderDTL>
	</orderHRD>
</Root>';

-- Method #1: Query nested XML nodes by traversing backward and forward.
-- Query Cost: 81%
select
	Tab.Col.value('../@ID', 'int') as OrderHDR_ID,
	Tab.Col.value('../custID[1]', 'int') as Cust_ID,
	Tab.Col.value('@ID', 'int') as OrderDTL_ID,
	Tab.Col.value('prodID[1]', 'int') as Prod_ID,
	Tab.Col.value('qty[1]', 'int') as QTY,
	Tab.Col.value('cost[1]', 'float') as Cost,
	Tab.Col.value('count(../orderDTL)', 'int') as Cust_Ord_Count
from @xml.nodes('/Root/orderHRD/orderDTL') Tab(Col)

-- Method #2: Query nested XML nodes by using CROSS APPLY on appropriate node.
-- Query Cost: 19%
select
	Tab.Col.value('@ID', 'int') as OrderHDR_ID,
	Tab.Col.value('custID[1]', 'int') as Cust_ID,
	Tab1.Col1.value('@ID', 'int') as OrderDTL_ID,
	Tab1.Col1.value('prodID[1]', 'int') as Prod_ID,
	Tab1.Col1.value('qty[1]', 'int') as QTY,
	Tab1.Col1.value('cost[1]', 'float') as Cost,
	Tab.Col.value('count(./orderDTL)', 'int') as Cust_Ord_Count
from @xml.nodes('/Root/orderHRD') as Tab(Col)
cross apply Tab.Col.nodes('orderDTL') as Tab1(Col1)
GO

Output:-
OrderHDR_ID Cust_ID OrderDTL_ID Prod_ID QTY Cost
101	    501	    201		1	5   25.12
101	    501	    202		2	3   30
102	    502	    203		11	12  140.78
103	    503	    204		6	8   60.35
103	    503	    205		9	2   10.5
103	    503	    206		10	6   120.89

Method #2 is much more effecient than first one. As 1st method uses forward-backward traversal approach. The 2nd method uses APPLY operator which gets Nodes information from a specific parent.

This topic was also discussed on MSDN TSQL’s forum few weeks back, link.
 

>> Check & Subscribe my [YouTube videos] on SQL Server.
 


Query XML string in tabular format – SQL Server

July 13, 2011 3 comments

My previous post on XML discusses how to simply SELECT records from an XML string.

On this post I’m giving few more examples on how to SELECT records from an XML string. Here are some more examples to retrieve data in a relational tabular format by querying an XML string:

DECLARE @xml XML

SET @xml=N'<Root>
    <stud id="1">
		<fname>Manoj</fname>
		<lname>Pandey</lname>
		<class>10</class>
		<marks>80.5</marks>
	</stud>
    <stud id="2">
		<fname>Saurabh</fname>
		<lname>Sharma</lname>
		<class>11</class>
		<marks>82.7</marks>
	</stud>
    <stud id="3">
		<fname>Kanchan</fname>
		<lname>Pandey</lname>
		<class>10</class>
		<marks>90.5</marks>
	</stud>
    <stud id="4">
		<fname>Rajesh</fname>
		<lname>Shah</lname>
		<class>11</class>
		<marks>70.3</marks>
	</stud>
    <stud id="5">
		<fname>Kunal</fname>
		<lname>Joshi</lname>
		<class>12</class>
		<marks>64.7</marks>
	</stud>
</Root>';

-- Select all records in tabular format from above XML string:
SELECT
Tab.Col.value('@id','int') AS ID,
Tab.Col.value('fname[1]','nvarchar(20)') AS FirstName,
Tab.Col.value('lname[1]','nvarchar(20)') AS LastName,
Tab.Col.value('class[1]','int') AS class,
Tab.Col.value('marks[1]','float') AS Marks
FROM @xml.nodes('/Root/stud') Tab(Col)
Output:-
ID FirstName LastName Class Marks
1  Manoj     Pandey   10    80.5
2  Saurabh   Sharma   11    82.7
3  Kanchan   Pandey   10    90.5
4  Rajesh    Shah     11    70.3
5  Kunal     Joshi    12    64.7
-- Select records as in Key-Value pair format:
SELECT
Tab.Col.value('../@id', 'int') AS RowID,
Tab.Col.value('local-name(.)', 'nvarchar(max)') AS ColName,
Tab.Col.value('text()[1]', 'nvarchar(max)') AS ColValue
FROM @xml.nodes('/*/*/*') AS Tab(Col)
GO
Output:-
RowID ColName ColValue
1     fname   Manoj
1     lname   Pandey
1     class   10
1     marks   80.5
2     fname   Saurabh
2     lname   Sharma
2     class   11
2     marks   82.7
3     fname   Kanchan
3     lname   Pandey
3     class   10
3     marks   90.5
4     fname   Rajesh
4     lname   Shah
4     class   11
4     marks   70.3
5     fname   Kunal
5     lname   Joshi
5     class   12
5     marks   64.7
Categories: XML Tags: ,

Check and ReSeed IDENTITY column value in a table

July 8, 2011 3 comments

In my [previous post] I discussed about IDENTITY property with a [demo video].

Here in this post we will see how you can Re-Seed a particular IDENTITY Column value.
 

There are times when you want to Re-Seed or reset the value of an identity column in a table.

When you delete a lot of records from a table and then try to insert more records. You would expect the identity values to start after the max value present after delete. But it preserves the max value ever present in the table and continues from there.

Or when you delete all records form a table and want to reseed the identity column value to start from afresh 1.
 

All you need is following statement, syntax:
DBCC CHECKIDENT (TableNameWithSingleQuotes, reSeed, NewseedValue);

-- Example:
DBCC CHECKIDENT ('Person.Contact', reseed, 100);

 

This will start assigning new values starting from 101. But make sure that there are no records that have value greater than 100, otherwise you might duplicates.

If all records are deleted from the table and you want to reseed it to 0 then you can also TRUNCATE the table again. This will reset the IDENTITY column as per the DDL of that table.
 

–> You can check the same demo here:


 

–> Sample SQL Code:

-- Check current IDENTITY value, and re-seeds the value with largest value of the column:
DBCC CHECKIDENT ('dbo.Employee'); -- do not use it.
GO

-- Re-seed IDENTITY column value:
DBCC CHECKIDENT ('dbo.Employee', reseed, 102);
GO

-- Check current IDENTITY value (without Re-seeding):
DBCC CHECKIDENT ('dbo.Employee', noreseed);
GO

INSERT INTO [dbo].[Employee] ([EmployeeName])
VALUES ('Deepak B')

INSERT INTO [dbo].[Employee] ([EmployeeName])
VALUES ('Manish A')
GO

SELECT * FROM [dbo].[Employee]
GO

 


Programming is like a Dream… by Lisa

July 5, 2011 Leave a comment

This post is different from my other previous posts as it does not talks about SQL or anything near to it. But it talks about the Progmmers who code not only in SQL but other programming languages too.

I stumbled into Lisa’s post which talks about Programming as a Dream. I liked this article very much and the illustration given by her really fits in each and every scenario in one’s day today life and specially in dreams.

An excerpt from her post:

“If you think about programming like dreaming, you’ll realize that programmers remember more if you interrupt them gently than if you barrage them. If someone wakes you out of bed and starts shouting a long list of things at you to remember, you’ll almost certainly forget what you were dreaming. However, if someone shakes you gently and gives you a few seconds to open your eyes and look around before they start talking, it’s a lot easier to remember the dream for later. The same works for programmers. If you just walk into their office and start talking, one of two things will happen: they’ll completely forget what they were coding, or they won’t really be paying attention to you. However, if you quietly walk up to them and let them know you are there but say nothing until they are ready, the programmer can come to the end of the thought they are on. Once they’ve finished their thought, it will be easier for them to pick up next time and still pay attention to what you have to say.”

To read the whole post here is the link for you to go thru: http://www.independentdeveloper.com/archive/2009/03/17/programming-is-like-a-dream

SQL Query for calculating Running Totals

July 4, 2011 4 comments

A running total is the summation of a sequence of numbers which is updated each time a new number is added to the sequence, simply by adding the value of the new number to the running total.

Let’s see how to get these Running totals by creating a simple SQL queries below:

USE [AdventureWorks]
GO

-- Method 1: (Query Cost 9%)
;with RunTot as (
select row_number() over(partition by s.SalesPersonID order by s.OrderDate) as row,
s.SalesPersonID, p.FirstName, p.LastName, s.TotalDue, s.OrderDate
from Sales.SalesOrderHeader s
join Person.Contact p
on s.SalesPersonID = p.ContactID)
select  a.row, a.SalesPersonID, a.FirstName, a.LastName, a.OrderDate, a.TotalDue, sum(b.TotalDue) as RunTotal
from RunTot a
join RunTot b on a.SalesPersonID = b.SalesPersonID and a.row >= b.row
group by a.row, a.SalesPersonID, a.FirstName, a.LastName, a.TotalDue, a.OrderDate
order by a.SalesPersonID, a.row

-- Method 2: (Query Cost 91%)
;with RunTot as (
select row_number() over(partition by s.SalesPersonID order by s.OrderDate) as row,
s.SalesPersonID, p.FirstName, p.LastName, s.TotalDue, s.OrderDate
from Sales.SalesOrderHeader s
join Person.Contact p
on s.SalesPersonID = p.ContactID)
select  row, SalesPersonID, FirstName, LastName, OrderDate, TotalDue,
(select SUM(TotalDue) from RunTot b where b.SalesPersonID=a.SalesPersonID and b.row<=a.row) as RunTotal
from RunTot a

-- Output Listed below:

Categories: SQL Tips Tags: