Bit of Xquery in SqlServer2005 :-Final part(Introductory)

Posted: March 1, 2006 in Uncategorized

Part1 :-http://spaces.msn.com/shreeman/blog/cns!E1AF7EB63FAA1FF!508.entry

Part2 :-http://spaces.msn.com/shreeman/blog/cns!E1AF7EB63FAA1FF!509.entry

Part3 :-http://spaces.msn.com/shreeman/blog/cns!E1AF7EB63FAA1FF!510.entry

We already have covered the basics of  xml method like nodes and  Values to complete the Xml methods i am going to show a little of the other 2 Query and the DML of Insert attribute and element as well as modify and finally delete to complete the introductory series.

 

declare @inxml xml

Set

@inxml=‘<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>

<Employee><empid>2</empid><name>ram</name><sal>2000</sal><deptno>30</deptno><deptname>PRocess</deptname>

<location>BLR</location></Employee>

</EMPOBJ>’

 

select

@inxml.query(‘/EMPOBJ/Employee’)

select

@inxml.query(‘/EMPOBJ/Employee/empid’)

–use same xml

declare

@f bit

set

@f = @inxml.exist(‘/EMPOBJ/Employee[1][deptno[1] eq 30]’)— now enter 10–you understabd i am comparing the top row now

select

@f

set

@f = @inxml.exist(‘/EMPOBJ/Employee[deptno[1] eq 30]’)

select

@f

set

@f = @inxml.exist(‘/EMPOBJ/Employee/deptno[1]’)

select

@f

 

— insert

 

SET

@inxml.modify(

insert (

<newelement1>i am new element1</newelement1>,

<newelement2>i am new element2</newelement2>

)

into (/EMPOBJ/Employee)[1] ‘

)

SELECT

@inxml;

 

— insert new attribute

 

SET

@inxml.modify(

insert attribute newattribute1 {“i m new attribute” }

into (/EMPOBJ/Employee)[1] ‘

)

SELECT

@inxml

— multiple insert

 

SET

@inxml.modify(

insert (

attribute attr1 {“attrib1” },

attribute attr2 {“attrib2”}

)

into (/EMPOBJ/Employee/location)[1] ‘

)

SELECT

@inxml;

 

— delete an attribute

 

SET

@inxml.modify(

delete /EMPOBJ/Employee/@location –put @ for attribute centirc xml

)

SELECT

@inxml

— delete an element

 

SET

@inxml.modify(

delete /EMPOBJ/Employee/location

)

SELECT

@inxml

 

 

Further this is going to be my final post ona  introductory note and as i mention before i am not an expert and often getting similar queries on this same lines from my friend i decided to put a post describing the basic s of the Xpath query in SQlServer2005.

Hope that you found this series helpful .

Plese comment …

 

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