XML Learning Web site

Http://tech.sina.com.cn/s/2006-09-19/09021146220.shtml

Learning notes:

Use [sino_commissions]
Go
/****** object:table [dbo].    [Par_ Performance Indicators] Script date:07/02/2010 10:52:42 ******/
SET ANSI_NULLS on
Go
SET QUOTED_IDENTIFIER ON
Go
CREATE TABLE [dbo]. [Par_ Performance Indicators] (
Name [nvarchar] (255) NULL,
[English name] [nvarchar] (255) NULL,
Superior [nvarchar] (255) NULL,
Sector [nvarchar] (255) NULL,
Positions [nvarchar] (255) NULL,
Quarter [Float] Null
Annual [Float] Null
[Performance indicators] [Float] Null
) on [PRIMARY]
Go
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Wang Xiaojun ', n ' Danny ', NULL, n ' sales department ', N ' Sales director ', 1, 2010, 200)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Wang Xiaojun ', n ' Danny ', NULL, n ' sales department ', N ' Sales director ', 2, 2010, 300)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Wang Xiaojun ', n ' Danny ', NULL, n ' sales department ', N ' Sales director ', 3, 2010, 350)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Wang Xiaojun ', n ' Danny ', NULL, n ' sales department ', N ' Sales director ', 4, 2010, 400)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Wangna ', n ' florawn ', NULL, n ' client department ', N ' account manager ', 1, 2010, 55)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Wangna ', n ' florawn ', NULL, n ' client department ', N ' account manager ', 2, 2010, 76)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Wangna ', n ' florawn ', NULL, n ' client department ', N ' account manager ', 3, 2010, 72)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Wangna ', n ' florawn ', NULL, n ' client department ', N ' account manager ', 4, 2010, 77)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Zhang Wenjing ', n ' Joyce_zhang ', NULL, n ' client department ', N ' account manager ', 1, 2010, 35)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Zhang Wenjing ', n ' Joyce_zhang ', NULL, n ' client department ', N ' account manager ', 2, 2010, 70)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Zhang Wenjing ', n ' Joyce_zhang ', NULL, n ' client department ', N ' account manager ', 3, 2010, 75)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Zhang Wenjing ', n ' Joyce_zhang ', NULL, n ' client department ', N ' account manager ', 4, 2010, 70)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Sanli ', n ' Landy ', NULL, n ' client department ', N ' account manager ', 1, 2010, 45)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Sanli ', n ' Landy ', NULL, n ' client department ', N ' account manager ', 2, 2010, 55)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Sanli ', n ' Landy ', NULL, n ' client department ', N ' account manager ', 3, 2010, 60)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Sanli ', n ' Landy ', NULL, n ' client department ', N ' account manager ', 4, 2010, 60)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Wang Juan ', N ' Sunny ', NULL, n ' client department ', N ' account manager ', 1, 2010, 49)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Wang Juan ', N ' Sunny ', NULL, n ' client department ', N ' account manager ', 2, 2010, 67)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Wang Juan ', N ' Sunny ', NULL, n ' client department ', N ' account manager ', 3, 2010, 64)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Wang Juan ', N ' Sunny ', NULL, n ' client department ', N ' account manager ', 4, 2010, 70)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Tim ', n ' Tim_wang ', NULL, n ' client department ', N ' account manager ', 1, 2010, 20)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Tim ', n ' Tim_wang ', NULL, n ' client department ', N ' account manager ', 2, 2010, 50)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Tim ', n ' Tim_wang ', NULL, n ' client department ', N ' account manager ', 3, 2010, 80)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Tim ', n ' Tim_wang ', NULL, n ' client department ', N ' account manager ', 4, 2010, 110)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Zeng ', n ' Tony_zeng ', n ' Xia Yantao ', n ' Sales two group ', N ' Sales consultant ', 1, 2010, 20)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Zeng ', n ' Tony_zeng ', n ' Xia Yantao ', n ' Sales two group ', N ' Sales consultant ', 2, 2010, 25)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Zeng ', n ' Tony_zeng ', n ' Xia Yantao ', n ' Sales two group ', N ' Sales consultant ', 3, 2010, 25)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Zeng ', n ' Tony_zeng ', n ' Xia Yantao ', n ' Sales two group ', N ' Sales consultant ', 4, 2010, 30)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' She ', n ' Brian ', n ' Wang Xiaojun ', n ' Sales three group ', N ' Sales director ', 1, 2010, 30)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' She ', n ' Brian ', n ' Wang Xiaojun ', n ' Sales three group ', N ' Sales director ', 2, 2010, 40)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' She ', n ' Brian ', n ' Wang Xiaojun ', n ' Sales three group ', N ' Sales director ', 3, 2010, 60)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' She ', n ' Brian ', n ' Wang Xiaojun ', n ' Sales three group ', N ' Sales director ', 4, 2010, 70)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Heng Jiangbin ', n ' Eric_heng ', n ' Xia Yantao ', n ' Sales two group ', N ' Sales consultant ', 1, 2010, 10)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Heng Jiangbin ', n ' Eric_heng ', n ' Xia Yantao ', n ' Sales two group ', N ' Sales consultant ', 2, 2010, 12)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Heng Jiangbin ', n ' Eric_heng ', n ' Xia Yantao ', n ' Sales two group ', N ' Sales consultant ', 3, 2010, 13)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Heng Jiangbin ', n ' Eric_heng ', n ' Xia Yantao ', n ' Sales two group ', N ' Sales consultant ', 4, 2010, 15)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Liu Yi ', n ' Irwin ', n ' Wang Xiaojun ', n ' sell a group ', N ' Sales director ', 1, 2010, 60)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Liu Yi ', n ' Irwin ', n ' Wang Xiaojun ', n ' sell a group ', N ' Sales director ', 2, 2010, 90)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Liu Yi ', n ' Irwin ', n ' Wang Xiaojun ', n ' sell a group ', N ' Sales director ', 3, 2010, 90)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Liu Yi ', n ' Irwin ', n ' Wang Xiaojun ', n ' sell a group ', N ' Sales director ', 4, 2010, 90)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Xia Yantao ', n ' Alex ', n ' Wang Xiaojun ', n ' Sales two group ', N ' Sales director ', 1, 2010, 80)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Xia Yantao ', n ' Alex ', n ' Wang Xiaojun ', n ' Sales two group ', N ' Sales director ', 2, 2010, 100)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Xia Yantao ', n ' Alex ', n ' Wang Xiaojun ', n ' Sales two group ', N ' Sales director ', 3, 2010, 125)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Xia Yantao ', n ' Alex ', n ' Wang Xiaojun ', n ' Sales two group ', N ' Sales director ', 4, 2010, 145)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Zhang Yushu ', n ' Beyond_zhang ', n ' Xia Yantao ', n ' Sales two group ', N ' Sales consultant ', 1, 2010, 20)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Zhang Yushu ', n ' Beyond_zhang ', n ' Xia Yantao ', n ' Sales two group ', N ' Sales consultant ', 2, 2010, 25)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Zhang Yushu ', n ' Beyond_zhang ', n ' Xia Yantao ', n ' Sales two group ', N ' Sales consultant ', 3, 2010, 25)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Zhang Yushu ', n ' Beyond_zhang ', n ' Xia Yantao ', n ' Sales two group ', N ' Sales consultant ', 4, 2010, 30)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Peng ', n ' Michael_zhao ', n ' Liu Yi ', n ' Sales group ', n ' Sales consultant ', 1, 2010, 10)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Peng ', n ' Michael_zhao ', n ' Liu Yi ', n ' Sales group ', n ' Sales consultant ', 2, 2010, 20)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Peng ', n ' Michael_zhao ', n ' Liu Yi ', n ' Sales group ', n ' Sales consultant ', 3, 2010, 35)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Peng ', n ' Michael_zhao ', n ' Liu Yi ', n ' Sales group ', n ' Sales consultant ', 4, 2010, 35)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Ai Hui ', n ' Edwin ', n ' Wang Xiaojun ', n ' data department ', n ' buyer ', 2, 2010, 0)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Ai Hui ', n ' Edwin ', n ' Wang Xiaojun ', n ' data department ', n ' buyer ', 1, 2010, 0)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Ai Hui ', n ' Edwin ', n ' Wang Xiaojun ', n ' data department ', n ' Buyer ', 3, 2010, 0)
INSERT [dbo]. [Par_ Performance Indicators] ([name], [English name], [superior], [department], [title], [quarter], [year], [performance indicator]] VALUES (n ' Ai Hui ', n ' Edwin ', n ' Wang Xiaojun ', n ' data department ', n ' buyer ', 4, 2010, 0)

SELECT * FROM Dbo.aa

Select Person ID from dbo.par_ people table

ALTER TABLE par_ People table add performance XML

Update X
Set Performance =r
From Par_ person table X
INNER JOIN
(
Select
B. Name,
CONVERT (XML,
'
< performance personnel id= "1" >
< annual value= ">"
<q1 value= "' +convert (varchar,a. Performance indicators) + '"/>
<Q2 value= "' +convert (varchar,b. Performance indicators) + '"/>
<q3 value= "' +convert (varchar,c. Performance indicators) + '"/>
<q4 value= "' +convert (varchar,d. Performance indicators) + '"/>
</Annual >
</Performance >
') r
From
(SELECT * from Dbo.par_ performance indicator where quarter =1) A,
(SELECT * from Dbo.par_ performance indicator where quarter =2) b,
(SELECT * from Dbo.par_ performance indicator where quarter =3) C,
(SELECT * from Dbo.par_ performance indicator where quarter =4) d
where a. Name =b. Name and C. Name =b. Name and C. Name =d. Name
) y
On X. Name =Y. Name

Select Person ID, performance from PAR_ staff table

SELECT performance. Query ('/performance/Annual/Q2 ')
From Par_ personnel table

SELECT Person ID, performance. Value (' (/performance/annual /q2/@value) [1] ', ' int ')
From Par_ personnel table

SELECT Count (*)
From Par_ personnel table
WHERE performance. Exist ('/performance/Annual/q2[@value >=30] ') = 1

UPDATE Par_ Staff Table
SET performance. Modify ('
Delete/performance/annual/Q5[1]
')
--select Personnel ID, performance
From Par_ personnel table
WHERE performance. Exist ('/performance/Annual/q2[@value >=30] ') = 1

UPDATE Par_ Staff Table
SET performance. Modify ('
Insert <q5 value= "/>" as Last
into (/performance/annual) [1]
')
--select Personnel ID, performance
From Par_ personnel table
WHERE performance. Exist ('/performance/Annual/q2[@value >=30] ') = 1

UPDATE Par_ Staff Table
SET performance. Modify ('
Insert <q5 value= "/> as"
into (/performance/annual) [1]
')
--select Personnel ID, performance
From Par_ personnel table
WHERE performance. Exist ('/performance/Annual/q2[@value >=30] ') = 1

UPDATE Par_ Staff Table
SET performance. Modify ('
Insert <q5 value= "/>"
Before (/performance/Annual/q1) [1]
')
--select Personnel ID, performance
From Par_ personnel table
WHERE performance. Exist ('/performance/Annual/q2[@value >=30] ') = 1

UPDATE Par_ Staff Table
SET performance. Modify ('
Insert <q5 value= "/>"
After (/performance/Annual/q1) [1]
')
--select Personnel ID, performance
From Par_ personnel table
WHERE performance. Exist ('/performance/Annual/q2[@value >=30] ') = 1

UPDATE Par_ Staff Table
SET performance. Modify ('
Insert <q5 value= "/>"
into (/performance/Annual/q1) [1]
')
--select Personnel ID, performance
From Par_ personnel table
WHERE performance. Exist ('/performance/Annual/q2[@value >=30] ') = 1

UPDATE Par_ Staff Table
SET performance. Modify ('
Delete/performance/annual/Q1[1]
')
--select Personnel ID, performance
From Par_ personnel table
WHERE performance. Exist ('/performance/Annual/q2[@value >=30] ') = 1

UPDATE Par_ Staff Table
SET performance. Modify ('
Delete/performance/annual/Q1
')
--select Personnel ID, performance
From Par_ personnel table
WHERE performance. Exist ('/performance/Annual/q2[@value >=30] ') = 1

UPDATE Par_ Staff Table
SET performance. Modify ('
Delete/performance/annual/q1[1]/@value
')
--select Personnel ID, performance
From Par_ personnel table
WHERE performance. Exist ('/performance/Annual/q2[@value >=30] ') = 1

SET @doc. Modify ('
delete/team/players/pitcher[
@name = "John Smoltz"]/@role ")

DROP TABLE Par_ Staff table
Go
SELECT * Into Dbo.par_ staff table
From Par_ personnel table _bak

DECLARE @doc XML
SELECT @doc = '
<team name= "Braves" >
<Players>
<pitcher name= "John Smoltz" role= "Closer" >
With the team since 1989
</Pitcher>
</Players>
</Team> '
SET @doc. Modify ('
Replace value of (/team/players/pitcher[
@name = "John Smoltz"]/text ()) [1]
With ' May ' start in 2005
SAFDA "
')
Select @doc. Value (' (/team/players/pitcher/@name) [1] ', ' varchar (100) ')
Select @doc. Value (' (/team/players/pitcher/text ()) [1] ', ' varchar (100) ')
Select @doc. Query ('/team/players/pitcher ')

SET @doc. Modify ('
Replace value of (/team/players/pitcher[
@name = "John Smoltz"]/@role) [1]
With (
if (/team/players/pitcher[
@name = "John Smoltz"]/@role = "Closer") Then
"Starter"
Else
"Closer"
)
')
Select @doc. Query ('/team/players/pitcher ')

DECLARE @doc XML
SELECT @doc = '
<team name= "Braves" >
<Players>
<pitcher name= "John Smoltz" role= "Closer" >
With the team since 1989
</Pitcher>
<pitcher name= "John Smoltz" role= "Closer" >
With the team since 1989
</Pitcher>
</Players>
</Team> '
SELECT Team.player.query ('. ') as pitcher
From @doc. Nodes ('/team/players/pitcher ')
Team (player)

Select Pct_id,call_test from
SINO.. Par_call_test for XML PATH (' Lzy ')