Archive

Archive for the ‘SQL Server Internals’ Category

Using DML with XML – UPDATE & DELETE (SQL Server)

October 31, 2011 Leave a comment

In my previous post we saw how we can INSERT records in a XML string. On this post we”ll see the UPDATE & DELETE operations by using same xml.modify() method.

DECLARE @xml XML

-- Taking the sample XML string we created in my previous post:
SET @xml=N'<Root>
  <stud id="0">
    <fname>Kanchan</fname>
    <lname>Pandey</lname>
    <class>10</class>
    <marks>90.5</marks>
  </stud>
  <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>Rajesh</fname>
    <lname>Shah</lname>
    <class>11</class>
    <marks>70.3</marks>
  </stud>
</Root>'

> UPDATE marks of Manoj to 78.2 (currently 80.5):

SET @xml.modify('
	replace value of (/Root/stud[@id="1"]/marks/text())[1]
	with "78.2"')

SELECT @xml

> UPDATE marks of Rajesh to 78.7 stored in a variable:

DECLARE @marks FLOAT
SET @marks = 78.7

SET @xml.modify('
	replace value of (/Root/stud[@id="3"]/marks/text())[1]
	with sql:variable("@marks")')

SELECT @xml

> DELETE Saurabh record where id = 2:

SET @xml.modify('
	delete (/Root/stud[@id="2"])')

SELECT @xml

Final XML:

Categories: XML Tags: , , ,

Save XML in a file | Read XML from a file (in SQL Server)

October 20, 2011 6 comments 

USE [AdventureWorks]
GO

-- Save XML records to a file:
DECLARE @fileName VARCHAR(50)

DECLARE @sqlStr VARCHAR(1000)
DECLARE @sqlCmd VARCHAR(1000)

SET @fileName = 'E:\SQL_Queries\test.xml'
SET @sqlStr = 'select TOP 50 [ContactID], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [EmailAddress], [Phone] from AdventureWorks.Person.Contact FOR XML PATH(''Contact''), ROOT(''Person.Contact'')'

SET @sqlCmd = 'bcp "' + @sqlStr + '" queryout ' + @fileName + ' -w -T'

EXEC xp_cmdshell @sqlCmd

Progress:-
NULL
Starting copy...
NULL
6 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 15     Average : (400.00 rows per sec.)
NULL

Check the file in the directory set above in @filename variable.

Now we will see how can we read the XML file saved above. Will store the XML in into a SQL Server XML variable
and query it with SELECT statement:

DECLARE @xmlStr XML

-- Storing XML records into a XML variable:

SELECT @xmlStr = BulkColumn
FROM OPENROWSET(BULK 'E:\SQL_Queries\test.xml', SINGLE_CLOB) AS X

The above statement gave me ERROR:
SINGLE_CLOB requires a double-byte character set (DBCS) (char) input file. The file specified is Unicode.
Because the XML BCP’d to the file above was saved in unicode format. So use SINGLE_NCLOB instead of SINGLE_CLOB.

SELECT @xmlStr = BulkColumn
FROM OPENROWSET(BULK 'E:\SQL_Queries\test.xml', SINGLE_NCLOB) AS X

This again gave me ERROR:
XML parsing: line 1, character 2034, illegal qualified name character
Because the file saved in xml file has CrLf (Carriage Return & Line Feed) in between.
So I removed the CrLf from the XML string as shown below.

SELECT @xmlStr = REPLACE(REPLACE(BulkColumn,CHAR(10),''),CHAR(13),'')
FROM OPENROWSET(BULK 'E:\SQL_Queries\test.xml', SINGLE_NCLOB) AS X

-- Querying the XML stored in @xmlStr variable with SELECT statement:
SELECT
	T.C.value('ContactID[1]', 'INT') as [ContactID],
	T.C.value('Title[1]', 'VARCHAR(8)') as [Title],
	T.C.value('FirstName[1]', 'VARCHAR(50)') as [FirstName],
	T.C.value('MiddleName[1]', 'VARCHAR(50)') as [MiddleName],
	T.C.value('LastName[1]', 'VARCHAR(50)') as [LastName],
	T.C.value('Suffix[1]', 'VARCHAR(10)') as [Suffix],
	T.C.value('EmailAddress[1]', 'VARCHAR(50)') as [EmailAddress],
	T.C.value('Phone[1]', 'VARCHAR(50)') as [Phone]
FROM @xmlStr.nodes('//Person.Contact/Contact') as T(C)
Categories: XML Tags: , , , , , , ,

Using DMLs with XMLs – INSERT (SQL Server)

October 17, 2011 1 comment

In my previous XML related posts I wrote about how can we query and SELECT XML strings & pull data from parent-child nodes and hierarchies. In this post we’ll see how we can modify that XML data by applying DMLs to it.

In this part let’s see how we can INSERT new records in a XML string:

DECLARE @xml XML

-- Creating sample XML string with single row:
SET @xml=N'<Root>
    <stud id="1">
		<fname>Manoj</fname>
		<lname>Pandey</lname>
		<class>10</class>
		<marks>80.5</marks>
	</stud>
</Root>';

-- Now check the sample row by querying it with SELECT statement:
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
DMLs in XML provides a common function xml.modify() to modify the XML string in 3 ways, i.e. INSERT, UPDATE & DELETE. Here we will see how we can INSERT new records at different positions in the sample XML created above:

--// Now INSERTing some more records:

-- 1. INSERT (basic)
SET @xml.modify('
insert <stud id="2">
		<fname>Saurabh</fname>
		<lname>Sharma</lname>
		<class>11</class>
		<marks>82.7</marks>
	</stud>
into (/Root)[1]');

-- 2. INSERT at beginning, first record
SET @xml.modify('
insert <stud id="0">
		<fname>Kanchan</fname>
		<lname>Pandey</lname>
		<class>10</class>
		<marks>90.5</marks>
	</stud>
as first
into (/Root)[1]');

-- 3. INSERT at end, last record
SET @xml.modify('
insert <stud id="3">
		<fname>Rajesh</fname>
		<lname>Shah</lname>
		<class>11</class>
		<marks>70.3</marks>
	</stud>
as last
into (/Root)[1]');

-- Let's check the records just inserted:
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
0 Kanchan Pandey 10 90.5
1 Manoj Pandey 10 80.5
2 Saurabh Sharma 11 82.7
3 Rajesh Shah 11 70.3

-- Check final XML with INSERTed records above:
SELECT @xml

In my next XML-DML post we will see UPDATE and DELETE operations.

Categories: XML Tags: , , ,

What are Records, Pages, Extents, Heap in SQL Server?

October 12, 2011 1 comment

 
I found this very interesting series by Paul Randal on some basic terms of SQL Server Storage Engine at MSDN SQL Server blog. This is a 4 part series and provides information on records, pages, extents and heap structures.

I’ve seen many people asking questions related to these topics on MSDN & other SQL Server forums. And there are very few tech articles on internet and MS BOL regarding these. Paul has very breifly and in layman terms explained these topics.
 

I’d like to share the following topics for my future reference and for my blog viewers:

Part 1. What are Rows/Records?

Part 2. What are Pages?

Part 3. What are Extents?

Part 4. What is a Heap?
 

–> PAGE: A page in SQL Server is an 8 KB data storage area. There are 8 types of pages:

1. Data page
2. Index page
3. IAM (Index Allocation Map) page
4. GAM (Global Allocation Map) and SGAM (Shared Global Allocation Map) page
5. PFS (Page Free Space) page
6. Text/Image page
7. Sort page
8. Differential Bitmap page
9. Bulk-Changed Map page
10. Boot page
11. FileHeader page

Categories: DB Concepts Tags: , , ,

more examples on querying XML …with CROSS APPLY & XQuery – in SQL Server

September 22, 2011 3 comments

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.
 

Categories: XML Tags: , , ,