Search

Advertisements

Showing posts with label XML INDEX. Show all posts
Showing posts with label XML INDEX. Show all posts

SQL Server - XML Examples(Xpath,Xquery,XML DML,XML Index,XML Schema)

Example 1:

Mostly we need  XML in this format,i.e column names with their values enclosed within their column name tag

Consider below XML needs to be generated for the below mentioned sample table definition

<Employee>
<field Name="ID">1</field>
<field Name="Name">Sathya</field>
<field Name="Age">25</field>
<field Name="Sex">Male</field>
<field Name="ID">2</field>
<field Name="Name">Sunny</field>
<field Name="Age">24</field>
<field Name="Sex">Female</field>
</Employee>

DECLARE @Employee TABLE
(ID INT,
Name VARCHAR(100),
Age INT,
Sex VARCHAR(50))

INSERT @Employee SELECT 1,'Sathya',25,'Male'
INSERT @Employee SELECT 2,'Sunny',24,'Female'

DECLARE @xmldata XML

SET @xmldata = (SELECT ID,Name,Age,Sex FROM @Employee FOR XML PATH (''))

SET @xmldata = (
SELECT ColumnName AS "@Name",
       ColumnValue AS "text()"
FROM(
SELECT i.value('local-name(.)','varchar(100)') ColumnName,
       i.value('.','varchar(100)') ColumnValue
FROM @xmldata.nodes('//*[text()]') x(i)) tmp
FOR XML PATH ('field'),root('Employee'))

SELECT @xmldata

Example 2:

If XML is stored as NTEXT and if we are required to make changes in the node values of that XML


Sample XML,

<Employee>
<field Name="ID">1</field>
<field Name="Name">Sathya</field>
<field Name="Age">25</field>
<field Name="Sex">Male</field>
</Employee>

All about XML in SQL Server

SQL Server handles two types of data

1.)Relational data
2.)XML data

I have covered almost all topics about handling XML in SQL Server and posted in my site.

SECTION - 1:

      How to convert relational data to XML data ?

      answer :       use FOR XML clause

For more information about FOR XML clause, refer below link

http://www.allaboutmssql.com/2013/11/handling-xml-data-in-sql-server.html


SECTION - 2



How to traverse through XML document/fragment to fetch/modify attribute values or element nodes?

answer use XQUERY & XPATH

 How to shred XML data into columns of relational tables? 

 answer : use nodes() &  OPENXML

 How to validate structure & values of XML,each time when it is inserted/updated?

 answer : use XML SCHEMA COLLECTION

 How to improve the performance of querying XML data ?

  answer : use XML INDEX - primary & secondary index.



For more information about XQUERY,XPATH,XMLINDEX,XMLSCHEMA clause, refer below link

http://www.allaboutmssql.com/2012/09/xqueryxpathxmlschemaxml-index_6.html

XQUERY,XPATH,XMLSCHEMA,XML INDEX



(XQUERY,XPATH,XMLSCHEMA,XML INDEX)


Ø  XQuery is the language for querying XML data.


   Ø  XPath was designed to navigate an XML document to retrieve the documents elements and attributes.


     Ø  XQuery is built on XPath expressions.

Ø To put it simple XQuery,XPath are used to traverse through XML document/fragment to fetch/modify attribute values or element nodes.



XQuery FLWOR Expression:


  • for - (optional) binds a variable to each item returned by the in expression
  • let - (optional) to assign value to variable
  • where - (optional) specifies a criteria
  • order by - (optional) specifies the sort-order of the result
  • return - specifies what to return in the result.


Ø  The xml data type features several built-in methods that allow you to manipulate XML instance data. 

These methods allow you to query, modify, or shred your XML data into relational form.


query()   -The query() method allows you to perform an XQuery on your xml instance.
The result returned is untyped XML. 

Syntax: DbObject.query('XQuery')


value()   - The value() method allows you to perform an XQuery on your xml instance and returns a scalar value cast to a SQL Server data type.

Syntax: DbObject.value('XQuery', 'SqlType')

               
exist() - The exist() method allows you to specify an XQuery on your xml instance and returns a SQL bit value of 1 if the XQuery returns a result, 0 if the XQuery returns no result, or NULL if the xml instance is NULL.

Syntax :DbObject.exist('XQuery')



modify()                 - The modify() method allows you to execute XML Data Manipulation Language (XML DML) statements against an xml instance. The modify() method can only  be used with a SET clause or statement.

Syntax :DbObject.modify('XQuery')


nodes() - The nodes() method allows you to shred xml instances. Shredding is the process of converting your XML data to relational form.

Syntax :DbObject.nodes('XQuery') AS TableAlias(ColumnAlias)



XML DML Keywords:

Delete - Deletes the node specified by an XQuery path expression.

Insert -  Inserts one or more nodes as the children or siblings of a node specified
by an XQuery path expression.

replace value of    -  Updates the value of a node specified by an XQuery path.



--Examples for XQUERY & XPATH

--lets us  create sample data

Advertisements