little Bit Of XQuery in SqlServer2005:-Part1

Posted: March 1, 2006 in Uncategorized

A little Bit Of XQuery in SqlServer2005:-

 

Before I am going to blog about the Xquery feature in SqlSever2005 let me Clarify that I am not an expert in this arena and nor do I am a Xquery Expert. Further What I am going to post is really can’t cover in one post but I ll try to cover as much as possible giving examples of the sqlserver2000 way of doing things as well as how the Xquery makes your life easier with the XML data type and how you can achieve the same result in more efficient way without using OPenXml and For Xml Explicit.

 

Let me start with the Xml data type in SqlServer.

With the New XMl data type what we get in hand is no need to use the Text and Ntext data types and the pointers .Not only that it provides a great deal of flexibility .Not only with optimization but faster execution too. No longer you have to think about the limitation of you can declare a Text and Ntext type to manipulate your data.

 

Example: – Update a Ntext Column:-

CREATEproc tnxt as 

declare @ptr1 binary(16) 

declare @ptr2 binary(16) 

createtable #t(id int,data ntext) 

insert #t values(1,’new ntext test today 4th april’) 

 

select @ptr1=textptr(bigdata)from testntext 

select @ptr2=textptr(data)from #t 

 

updatetext testntext.bigdata @ptr1 NULL 0 #t.data  @ptr2 

droptable #t

 

Further you can have the Xml DML(you can modify ur xml ..) statements to manipulate ur xml as well as you can defined Index over ur xml columns and you can ve the schema too built-in and stored in ur database Thus you can have both the typed and untyped Xml .

 

 

I am now not going to write the details of the usefulness of Xml Datatype further you can get the details here: http://msdn.microsoft.com/sql/learning/prog/xml/default.aspx

However what I am going to cover here is in simplistic way I am going to show how you can leverage the Xml features to implement your task much simpler way then the sqlserver2000 says. I am going to show the FOR XML EXPLICIT vs FOR XML PATH and how you can replace (almost) the OPENXML and sp_xml_prepareDoc & removed (if you forgot to release the doc you are leaking huge memory).

 

I am going to show you how you can handle Element Centric Data as well as AttributeCentirc data as well as some sort of ParentChild Relationship. Further if I get time I am going to show you the basics of Xml Methods: –  Exists, Query, Value and Nodes etc;

 

I know this is very little for the vast topic but this will provide a starting platform for beginners handle the new features and for the details you can always refers to the above link.

 

 

Old technique (How you handle Xml in Sql2000) with Element centric xml:-

declare @xml xml -–even this is the new datatype and you need to fallback to varchar or nvarchar in sql2000

 

set @xml='<EMPOBJ>

  <Employee>

    <empid>1</empid>

    <name>shreeman</name>

    <sal>1070</sal>

    <deptno>10</deptno>

    <deptname>admin</deptname>

    <location>Hyd</location>

  </Employee>

  <Employee>

    <empid>0</empid>

    <name>raj</name>

    <sal>500</sal>

    <location>del</location>

  </Employee>

 </EMPOBJ>’

DECLARE @idoc int

EXECsp_xml_preparedocument @idoc OUTPUT, @xml

 

SELECT    *

FROM       OPENXML(@idoc,’/EMPOBJ/Employee’,2)

            WITH(            empid               int                                ,

                                                            name                 varchar(20)      ,

                                                            sal                                int                                ,

                                                            deptno              int                                ,

                                                            deptname          varchar(20)      ,

                                                            locatoion          varchar(20)

                                                  )

EXECsp_xml_removedocument @idoc

New(SQLSERVER 2005) Equivalent With ELementCentric :-

declare @inxml xml

Set @inxml=’Use the same Xml as Above

     SELECT  U.value(‘./empid[1]’,’Varchar(20)’)     AS [empid]    

           , U.value(‘./name[1]’,’Varchar(20)’)                AS [name]

           , U.value(‘./sal[1]’,’Int’)                AS [sal]

           , U.value(‘./deptno[1]’,’Int’)                AS [deptno]

           , U.value(‘./deptname[1]’,’Varchar(50)’)     AS [deptname]

           , U.value(‘./location[1]’,’Varchar(20)’)                   AS [location]

     FROM @inXML.nodes(‘/EMPOBJ/Employee’)AS T(U)

 

Old technique(Sqlserver2000) with attribute centric xml:-

 

declare @xml xml -–even this is the new datatype and you need to fallback to varchar or nvarchar in sql2000

 

set @xml='<EMPOBJ>

<Employee empid=”1″ name=”shreeman” sal=”1070″ deptno=”10″  deptname=”admin” location=”HYD”/> 

<Employee empid=”0″ name=”raj” sal=”500″  location=”DEL”/>   

</EMPOBJ>’

DECLARE @idoc int

EXECsp_xml_preparedocument @idoc OUTPUT, @xml

 

SELECT    *

FROM       OPENXML(@idoc,’/EMPOBJ/Employee’,1)

            WITH(            empid               int                                ,

                                                            name                 varchar(20)      ,

                                                            sal                                int                                ,

                                                            deptno              int                                ,

                                                            deptname          varchar(20)      ,

                                                            locatoion          varchar(20)

                                                  )

EXECsp_xml_removedocument @idoc

 

New Equivalent(SqlServer 2005) With Attribute Centric :-

 

declare @inxml xml

Set @inxml=’ Use the same Xml as Above

 

     SELECT  T.U.value(‘@empid’,’int’)                     AS [empid]    

           , T.U.value(‘@name’,’Varchar(20)’)                AS [name]

           , T.U.value(‘@sal’,’Int’)                AS [sal]

           , T.U.value(‘@deptno’,’Int’)                AS [deptno]

           , T.U.value(‘@deptname’,’Varchar(20)’)     AS [deptname]

           , T.U.value(‘@location’,’Varchar(20)’)                   AS [location]

     FROM @inXML.nodes(‘/EMPOBJ/Employee’)AS T(U)

 

 

Due to the space limitation per post I am keeping this post smaller and I ll  2 other posts soon to complete(J I should better not use this word ) the introduction of Xml features in sqlserver2005.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s