Archive
more examples on querying XML …with CROSS APPLY & XQuery – in SQL Server
My quest to know more about XML and to use it in SQL language always encourages me to google this topic and religiously follow the MSDN’s SQL Server XML forum. I liked this post on XML, it contains some more ways to query XML data cited by some SQL experts.
In my previous posts I’ve shown some practical examples on how to query your XML data or string and get results in tabular format. Following are the links I’ve previously posted on this topic:
1. https://sqlwithmanoj.com/2011/01/28/select-an-xml-to-table/
2. https://sqlwithmanoj.com/2011/07/13/query-xml-string-in-tabular-format/
3. https://sqlwithmanoj.com/2011/07/13/select-or-query-nodes-in-hierarchial-or-nested-xml/
–> Let’s see one more example and multiple ways to query an XML string:
DECLARE @XML XML
SET @XML =
'<Input>
<Courses>
<Course>
<Id>27</Id>
<Students>
<Id>19876</Id>
<Id>19878</Id>
</Students>
</Course>
<Course>
<Id>29</Id>
<Students>
<Id>19879</Id>
</Students>
</Course>
</Courses>
</Input>'
-> Desired Output: CourseId Students 27 19876 27 19878 29 19879
-- Method# 1. Simple approach but bit costly | Query Cost: 83%
select t.c.value('../../Id[1]', 'INT') as CourseId,
t.c.value('.', 'INT') as Students
from @XML.nodes('//Input/Courses/Course/Students/Id') as t(c)
-- Method# 2. By using Cross Apply | Query Cost: 17%
select t.c.value('Id[1]', 'INT') as CourseId,
t1.c1.value('.', 'INT') as Students
from @XML.nodes('//Input/Courses/Course') as t(c)
cross apply t.c.nodes('Students/Id') as t1(c1)
The above 2 approaches shows that the second one with CROSS APPLY is much more performant.
– The traditional approach (#1) traverses the nodes (parent/child) and pulls the desired data.
– But the 2nd one with APPLY clause fetches specific node’s entire row and join it with the SELECTed data.
The NODES() function allows us to identify a particular node and map it into a new row. As the NODES function returns a rowset thus it can be queried by a SELECT statement or used like a UDF by applying APPLY clause. More on NODES(), here’s the link.
–> Let’s see an another approach by applying XQuery in a QUERY() function. The XQuery is a string, an XQuery expression, that queries for XML nodes such as elements, attributes, in an XML instance. More on QUERY(), here’s the link.
-- Method# 3. By using XML Query:
DECLARE @temp XML
set @temp = @XML.query('
for $a in /Input/Courses/Course/Id,
$b in /Input/Courses/Course/Students/Id
where $a/.. is $b/../..
return element detail {attribute CourseID {string($a)},attribute StudentID {string($b)}}')
select t.c.value('@CourseID','int') as [CourseID],
t.c.value('@StudentID','int') as [StudentID]
from @temp.nodes('/detail') as t(c)
Will see some more examples & more stuff on XML in my forthcoming posts.
>> Check & Subscribe my [YouTube videos] on SQL Server.
Stored Procedures vs Functions (Difference between SP & UDF) – SQL Server
Stored Procedures can contain a single SQL statement or a group of SQL statements with data flow control logic containing IF-ELSE, WHILE loop constructs, TRY-CATCH, transactions, etc.
SPs are used to return one or many result-sets to its calling application.
On the other hand Functions or UDFs can contain single or multiple SQL statements depending on its type. A Scalar UDF & Inline UDF can only have a single SELECT statement. And a Multi-Statement UDF can contain a body with multiple SQL statements including SELECTS, IF-ELSE, WHILE loops and DMLs but limited to manipulating table variables only.
UDFs return a single Scalar value or a Table variable to the calling SELECT statement.
Following are some common differences between an SP & a UDF:
–> Stored Procedures (SP):
– Can be used to read and modify data.
– To run an SP Execute or Exec is used, cannot be used with SELECT statement.
– Cannot JOIN a SP in a SELECT statement.
– Can use Table Variables as well as Temporary Tables inside an SP.
– Can create and use Dynamic SQL.
– Can use transactions inside (BEGIN TRANSACTION, COMMIT, ROLLBACK) an SP.
– Can use used with XML FOR clause.
– Can use a UDF inside a SP in SELECT statement.
– Cannot be used to create constraints while creating a table.
– Can execute all kinds of functions, be it deterministic or non-deterministic.
–> Functions (UDF):
– Can only read data, cannot modify the database.
– Can only be used with SELECT statement, JOINS & APPLY (CROSS & OUTER).
– Can JOIN a UDF in a SELECT statement.
– Cannot use a Temporary Table, only Table Variables can be used.
– Cannot use a Dynamic SQL inside a UDF.
– Cannot use transactions inside a UDF.
– Cannot be used with XML FOR clause.
– Cannot execute an SP inside a UDF.
– Can be used to create Constraints while creating a table.
– Cannot execute some non-deterministic built-in functions, like GETDATE().
More about “User Defined Functions” (UDFs) [check here].
>> Check & Subscribe my [YouTube videos] on SQL Server.
BCP { IN | OUT | QUERYOUT } – the BCP util in SQL Server
As per MS BOL the BCP utility bulk copies data between an instance of SQL Server and a data file in a user-specified format. The BCP utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files.
BCP or Bulk Copy Program is a command line utility that runs on DOS console. It is not a SQL statement, to run it from SQL SSMS you must use the xp_cmdshell Extended Stored Procedure as shown below.
Now let’s check with few examples how we can use the BCP Utility:
–> BCP OUT
-- Bulk Copy All Table records to a File SELECT * FROM Person.Address exec master..xp_cmdshell 'BCP AdventureWorks.Person.Address OUT d:\PersonAddressAll.txt -T -c'
-- Processing status on Result Pane NULL Starting copy... 1000 rows successfully bulk-copied to host-file. Total received: 1000 1000 rows successfully bulk-copied to host-file. Total received: 2000 ... 1000 rows successfully bulk-copied to host-file. Total received: 18000 1000 rows successfully bulk-copied to host-file. Total received: 19000 NULL 19614 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total : 250 Average : (78456.00 rows per sec.) NULL
–> BCP QUERYOUT
-- Bulk Copy Selected (Top 20) records by using Query in BCP command from Table to a file SELECT TOP 20 AddressID, AddressLine1, City, StateProvinceID, PostalCode, ModifiedDate FROM Person.Address exec master..xp_cmdshell 'BCP "SELECT TOP 20 AddressID, AddressLine1, City, StateProvinceID, PostalCode, ModifiedDate FROM AdventureWorks.Person.Address" QUERYOUT d:\PersonAddressByQuery.txt -T -c'
-- Processing status on Result Pane NULL Starting copy... NULL 20 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total : 1 Average : (20000.00 rows per sec.) NULL
–> BCP IN
-- Bulk Copy All File records to an existing Table
-- Create a similar table first
IF OBJECT_ID('AdventureWorks.Person.Address2') IS NOT NULL
DROP TABLE AdventureWorks.Person.Address2
SELECT *
INTO AdventureWorks.Person.Address2
FROM AdventureWorks.Person.Address
WHERE 1=2
exec master..xp_cmdshell 'BCP AdventureWorks.Person.Address2 IN d:\PersonAddressAll.txt -T -c'
-- Check newly inserted records
SELECT *
FROM AdventureWorks.Person.Address2 -- (19614 row(s) affected)
Options used in above queries:
-T : Trusted connection, here username & password are not required.
-c : Performs the operation using a character data type.
Note: In case of no Trusted connection, you must have to use the -U & -P options, like: “-U username -P password” instead of “-T”. If “-P password” is not applied the password is asked when the command is submitted, thus it is also a secure way of entering a masked password.
If you are not on default instance than there will be need to provide the Server instance name also by adding -S option, like: “-S ServerName”.
>> Check & Subscribe my [YouTube videos] on SQL Server.
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 