SQL Data Services (SDS) Part VI

In the previous SDS posts, we discussed how to create database, create table in the cloud by writing a query. We have also seen how to write T-SQL statements with SQL Azure.

In this post, we will discuss how XML data type works with SQL Azure using some sample queries:

How let clause works with FLOWR expression in SQL Azure

--Let clause with FLOWR expression for XML is supported in SQL Azure

declare @x xml = '';
select @x.query('
for $i in (1,2,3,4)
return $i')
go
-- returns 1 2 3 4

declare @x xml = '';
select @x.query('
for $i in ("A","B","C")
order by $i descending
return $i')
go
-- returns  C B A

declare @x xml = '';
select @x.query('
let $x := 1
return $x')
go
-- returns 1

declare @x xml = '';
select @x.query('
let $x := ( <one>2</one> )
return $x')
go
-- error:
-- XQuery [query()]: let  is not supported with constructed XML

-- When we use let inside a loop, it is evaluated each time for the loop

declare @x xml = '';
select @x.query('
for $i in (1,2)
let $j := "try"
return ($i, $j)')

-- returns 1 try 2 try
-- $j is evaluated 2 times

How XQuery works with SQL Azure

CREATE TABLE #Depts
(DeptID integer IDENTITY PRIMARY KEY,
 DeptName nvarchar(40),
 Manager nvarchar(40),
 Names xml)

INSERT INTO #Depts
VALUES
('SQL zure','Sane','<Names>
<Name FirstName="Geeta" LastName="Sohoni"/>
<Name FirstName="Mani" LastName="Raje"/>
<Name FirstName="Raja" LastName="Tembhe"/>
</Names>')

INSERT INTO #Depts
VALUES
('SQL Server','Dani','<Names>
<Name FirstName="Suruchi" LastName="Risbud"/>
</Names>')

INSERT INTO #Depts
VALUES
('SQL Server 2005','Kulkarni',NULL)

SELECT * FROM #Depts

The result is as follows:

The following query gives similar results:

SELECT DeptID, DeptName,Manager,Names.query('
/Names/Name')
FROM #Depts

The result of following query:

SELECT DeptID, DeptName,Manager,Names.value('
(/Names/Name/@FirstName)[2]','char(10)') SecondPerson
FROM #Depts

is as follows:

as the last 2 records have a single person

To fetch the Manager Name

SELECT DeptName,Manager FROM #Depts
WHERE Names.exist('/Names/Name/@FirstName[1]') = 1

The following 2 queries gives exclusive results -- one returns data where there are no people under manager and the other where at least one person has a manager

SELECT DeptName,Manager FROM#Depts
WHERENames.exist('/Names/Name/@FirstName[1]') = 0

--using exist

SELECTDeptName,Manager FROM#Depts
WHERENames.exist('/Names/Name') = 1

The following query will insert one of the relational column in XML as though it is an XML tag

SELECT DeptName, Names.query('<Names>
    <Mgr>{sql:column("Manager")}</Mgr>
    {
     for $i in /Names/Name
     return $i
    }
    </Names>')
FROM #Depts

--use modify method and insert a column
UPDATE #Depts
SET Names.modify('insert element Peon {"Raju"}
  as first
  into (/Names)[1]')
WHERE DeptID = 1

--delete the newly added tag
UPDATE #Depts
SET Names.modify('delete (/Names/Peon)[1]')
WHERE DeptID = 1