Click here to Skip to main content
16,017,100 members
Articles / Database Development / SQL Server / SQL Server 2008

XQuery basics

Rate me:
Please Sign up or sign in to vote.
4.67/5 (9 votes)
19 Jun 2013CPOL4 min read 42.7K   17   7
XQuery basics.

Introduction

XML has much scope in transporting and persistence data to take advantages of XML capabilities in development. Generally for data synchronization XML is used to make data synchronization more verbose. In SQL server 2005 XQuery support in database engine introduced to make querying structured XML data. XQuery is generally work with XPath expressions with additional support of node Iteration, sorting and in constructing of desired XML instance for various purpose.

XQuery follows the XPath 1.0 expressions. The XPath 1.O contains general syntax to retrieve specific node information from structured XML instance.

XSLT is another language using for XML Transforms .XSLT also follows the XPath 1.0 rules to transforms data from XML data. Generally XSLT used for XML transformation to HTML. XSLT is good for sequential nodes but not work with sequence of values. It’s difficult to apply joins and functions in XSLT so in this manner XQuery wins because you can use functions and joins in XQuery with ease.

In SQL Server using Open XML is good in terms of data shredding for large XML DATA files but for small XML DATA files it’s more memory intensive than XQuery. We can apply joins and functions on XML data as per requirement. It’s providing better code manageability.

This article is just to show how XQuery work by explaining some scenarios:

There are various advantages of using XQuery instead of Open XML.

  • Shorter XML syntax comparison of similar SQL syntax.
  • Flexible query structure to get tree view and tabular result.
  • Built in XQuery functions.

Overview: This article just for the beginners who want to start with XQuery in easy way .This demonstrates how you can query and manipulate xml data in SQL Server using XQuery. I am not going deep as its too big topic to understand technically, as in this article I just want to show how beginners can quick start with XQuery by giving some simple demonstrations.

Here we are going to understand some XQuery method by demonstrating some scenarios:

SQL
CREATE TABLE #TempXML(
      [DocId] [int] IDENTITY(1,1) NOT NULL,
      [Doc] [xml] NULL

)
CREATE TABLE #TempRegion(
      [RegionId] [int] IDENTITY(1,1) NOT NULL,
      [Region] [varchar](100) NULL

)

To start here creating two xml instance stored in sql temp table one without namespace and other with default namespace to understand how XQuery work with these types

Insert Into #TempXML Values ('<Persons>

SQL
<Person>
    <Name>Demo1</Name>
    <Salary>10000</Salary>
   
<Designation>Developer</Designation>
    <RegionId>1</RegionId>
  </Person>
  <Person>
    <Name>Demo2</Name>
    <Salary>12000</Salary>
    <Designation>Sr.
Developer</Designation>
    <RegionId>2</RegionId>
  </Person>
  <Person>
    <Name>Demo3</Name>
    <Salary>13000</Salary>
    <Designation>Sr.
Developer</Designation>
    <RegionId>3</RegionId>
  </Person>
  <Person>
    <Name>Demo4</Name>
    <Salary>14000</Salary>
   
<Designation>Developer</Designation>
    <RegionId>5</RegionId>
  </Person>
</Persons>')
Insert Into #TempXML Values ('<Persons
xmlns="http://schemas.test.App">
  <Person>
    <Name>Demo1</Name>
    <Salary>10000</Salary>
   
<Designation>Developer</Designation>
    <RegionId>1</RegionId>
  </Person>
  <Person>
    <Name>Demo2</Name>
    <Salary>12000</Salary>
    <Designation>Sr.
Developer</Designation>
    <RegionId>2</RegionId>
  </Person>
  <Person>
    <Name>Demo3</Name>
    <Salary>13000</Salary>
    <Designation>Sr.
Developer</Designation>
    <RegionId>3</RegionId>
  </Person>
  <Person>
    <Name>Demo4</Name>
    <Salary>14000</Salary>
   
<Designation>Developer</Designation>
    <RegionId>5</RegionId>
  </Person>

</Persons>')
Insert
#TempRegion Values ('East')
Insert
#TempRegion Values ('West')
Insert
#TempRegion Values ('North')

Insert #TempRegion Values ('South')

query() Method:The query() method is for provided XML nodes and fragmented XML document by evaluating XPath expressions. Query method takes "FLOWR" expression as well to retrieve relative untyped XML instance result.

Querying Person(s) with salary >12000 from XML (without Namespace):

SQL
Select 
Doc.query('/Persons/Person[Salary>12000]')
From #TempXML

WHERE DocId=1

Querying Person(s) with salary >12000 from XML instance where default namespace specified:

Here are two methods to deal with default namespace

SQL
;WITH
XMLNAMESPACES( default 'http://schemas.test.App' )
Select 
Doc.query('/Persons/Person[Salary>12000]')
From #TempXML

WHERE DocId=2
Select 
Doc.query('declare default element
namespace "http://schemas.test.App"; 
           /Persons/Person[Salary>12000]')
From #TempXML

WHERE DocId=2

If XML Namespace is not Default instance and declare as

Xmlns:PER=http://schemas.test.App

By declaring XQuery Prolog

declare namespace PER="http://schemas.test.App";
  • The XQuery prolog includes a namespace prefix (PER) declaration,
    (Namespace PER="http://schemas.test.App";
  • The declare namespace keyword defines a namespace prefix that is used later in the query body.
  • /PER:Persons/PER:Person[Name="Demo"] is the query body.

Using FLOWR Expression

FLWOR is pronounced "flower". FLOWR stands for FOR , LET, ORDER BY, WHERE and RETURN. Most of these are optional only return clause is mandatory with one of FOR or LET clause at least.FlOWR expression also used for querying or iteration XML instance .Here are few example to understand how it’s work in SQL.

Querying for Person(s) with salary >=12000

SQL
Select 
Doc.query('for $A in
/Persons/Person/Salary[.>="12000"]/..
              return 
              $A ')
From #TempXML

WHERE DocId=1

Querying for Person(s) with

SQL
salary >=12000 (Use of WHERE and ORDER BY)
Select 
Doc.query('for $A in /Persons/Person
             where $A/Salary>=12000
             order by $A/Name[1]
descending            
             return $A')
From #TempXML

WHERE DocId=1

Querying for Person(s) with salary <=12000 and adding 1200 to salary (Use of LET)

SQL
Select
 Doc.query('for $T in /Persons/Person
            let $L := 1200
            where $T/Salary[1]<=12000
            return
               <Person
Name="{data($T/Name)}"
RestructuredSalary="{data($L)+($T/Salary/text())[1]}"/>')
FROM #TempXML

Where DocId=1

value() Method: Method is used for retrieving scalar text values from XML instance. Relevant XQuery expression which identifies singleton node and text value type returns the node text value. Node value returned cast to provided SQL type.

SQL
;WITH
XMLNAMESPACES( default 'http://schemas.test.App' )
SELECT 
Name=x.value('(Name/text())[1]','varchar(100)'),
Salary=x.value('(Salary/text())[1]','varchar(100)'),
Designation=x.value('(Designation/text())[1]','varchar(100)')
From #TempXML 
     CROSS APPLY Doc.nodes('/Persons/Person') as e(x)

Where DocId=2

In below scenerio we are extracting Regions currently assigned to person(s) by applying JOIN between XML instance and user table #TempRegion

SQL
SELECT tR.Region
       FROM  #TempXML tX Left JOIN
             #TempRegion tR 
             on
tX.Doc.exist('/Persons/Person/RegionId[.=sql:column("tR.RegionId")]') = 1

       Where DocId=1

If you want to extract particular person(s) node by Zone then you can get this as below:

SQL
SELECT tX.Doc.query('/Persons/Person[RegionId=sql:column("tR.RegionId")]'),tR.Region
       FROM  #TempXML tX Left JOIN
             #TempRegion tR 
             on
tX.Doc.exist('/Persons/Person/RegionId[.=sql:column("tR.RegionId")]') = 1

       Where DocId=1

Using Functions in Xquery

Retrieving Person(s) count those salary >12000 (Use of count)

SQL
Select 
PersonCount=Doc.value('count(/Persons/Person[Salary>12000])','int')
From #TempXML
WHERE DocId=1

Retrieving Average salary of Person(s) those salary >12000 (Use of avg)

SQL
Select 
PersonCount=Doc.value('avg(/Persons/Person[Salary>12000]/Salary)','int')
From #TempXML
WHERE DocId=1

Xquery has various built in function listed below:

String Functions

  • concat: Concatenate string instance for similar uses in SQL.

  • contains: Searches the specified string.

  • substring: Retrieves part of string from other string
  • string-length: Determines the length of specified string

Aggregate Functions: min (), max (), and sum ()

Context Functions: last () and position ()

Manipulating Data in XML instance

To do the modification in XML nodes and their values, the XQuery XML query language conform an additional room known as the XML Data Modification Language(DML) by using insert, replace value of and delete in XQuery modify() method.

Inserting XML instance to Node of Person whose Name =Demo1

SQL
UPDATE #TempXML
SET
    Doc.modify('replace value of 
                
(/Persons/Person[DeploymentStatus[@IsDeployed="True"]]/Salary/text())[1]

                   with ("15000")')

WHERE DocId=1

Replace Salary node text with 15000 person name with Deployment Status value to True

SQL
UPDATE #TempXML
SET
    Doc.modify('replace value of 
                
(/Persons/Person[DeploymentStatus[@IsDeployed="True"]]/Salary/text())[1]

                   with ("15000")')

WHERE DocId=1

Deleting DeploymentStatus node from Person with name Demo1

SQL
UPDATE #TempXML
SET
    Doc.modify('delete
(/Persons/Person[Name="Demo1"]/DeploymentStatus)') 

WHERE DocId=1

Tip: Xquery To Insert Data into Master and refrenced Child table simultaneously:

There are n number of way to achieve this, This is a hint to insert data in master and refrenced child table using XQuery and Merge statement.

SQL
Create table #Master
(
 Id int Identity(1,1),
 MasterName Varchar(100)
)

Create Table #Intermediate
(MasterId int,
 ChildData XML)
 
 Create Table #Child
 (
 ChildId int identity(1,1),
 MasterId int,
 ChildName Varchar(100)
 )

Declare @XML XML='<Record>
                     <MasterRecord>
                       
<Master>Master1</Master>
                        <ChildRecord>
                            <Child>Child11</Child>
                           
<Child>Child12</Child>
                        </ChildRecord>
                     </MasterRecord>
                     <MasterRecord>
                       
<Master>Master2</Master>
                        <ChildRecord>
                           
<Child>Child21</Child>
                           
<Child>Child22</Child>
                        </ChildRecord>
                     </MasterRecord>
                  </Record>'
                  
  
        
MERGE #Master
_MTR
USING (Select x.value('Master[1]','varchar(255)') AS masterName ,
              x.query('ChildRecord/Child') AS ChildData          
              From
@XML.nodes('/Record/MasterRecord')
e(x)) AS _XML
ON 1=0
WHEN NOT MATCHED THEN
INSERT (MasterName)
VALUES(_XML.MasterName)
OUTPUT INSERTED.Id, _XML.ChildData
INTO
#Intermediate(MasterId,ChildData);


Insert Into #Child(MasterId,ChildName) 
Select  mas.Id, _data.value('(text())[1]', 'varchar(100)') as ChildName
from
#Intermediate intr Inner Join #Master Mas
     On intr.MasterId=Mas.Id
CROSS APPLY ChildData.nodes('/Child') AS _chd(_data)

Select * from #Master
Select * from #Intermediate

select * from #Child

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
India India
It's good to do best whatever you are doing....
Developed Windows and Web based solutions using Microsoft Technologies C#,C++,VC++.Net,MS Sql Server,Biz Talk...

Comments and Discussions

 
QuestionManipulating Data in XML instance .XQuery basics Pin
yup20105-Nov-19 18:37
yup20105-Nov-19 18:37 
QuestionGet the XML element specifying Index dynamically Pin
fvalerin22-May-14 11:40
fvalerin22-May-14 11:40 
Good article, thank you.

Do you know if I can do something similar to this code : getting the second or third or fourth element of the XML but specifying the index dynamically with a variable :

Not this :
SELECT  @myDoc.value('(/Root/ProductDescription/@ProductID)[ 3 ]', 'int' )


But something like this (which would be the right syntax ???, I dont care if its totally different or using another function, method, etc) :
DECLARE @Index int
DECLARE @myDoc xml
 
SET @Index = 3
SET @myDoc = '<Root>
                <ProductDescription ProductID="9865" ProductName="Road Bike"> 
                        .....'
 
SELECT  @myDoc.value('(/Root/ProductDescription/@ProductID)[ =sql:variable("@Index") ]', 'int' )

thanks, fvalerin

AnswerRe: Get the XML element specifying Index dynamically Pin
Mayank Dubey22-May-14 21:40
Mayank Dubey22-May-14 21:40 
GeneralRe: Get the XML element specifying Index dynamically Pin
fvalerin6-Jun-14 9:26
fvalerin6-Jun-14 9:26 
GeneralMy vote of 5 Pin
Ștefan-Mihai MOGA13-Jul-13 20:46
professionalȘtefan-Mihai MOGA13-Jul-13 20:46 
GeneralMy vote of 4 Pin
Sudhakar Shinde1-Jul-13 3:18
Sudhakar Shinde1-Jul-13 3:18 
QuestionGreat article Pin
Alejandro Hellin26-Jun-13 11:10
Alejandro Hellin26-Jun-13 11:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.