Archive
SELECT or Query nodes in hierarchial or nested XML
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
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
Check and ReSeed IDENTITY column value in a table
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
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
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:
Current Visitors
StatCounter …since April 2012
Leisure blog: Creek & Trails
- An error has occurred; the feed is probably down. Try again later.
SQL with Manoj 