SQL Server FOR XML PATH文の応用
4408 ワード
参照してください:
http://msdn.microsoft.com/zh-cn/library/ms178107.aspx
【転載】http://www.cnblogs.com/yanghaibo/archive/2010/06/04/1751405.html
SQL Serverでは、FOR XML PATH文を利用してクエリのデータをXMLデータを生成することができます。以下はそのいくつかのアプリケーションの例です。
DECLARE
@TempTable
テーブル(UserID
要点 , UserName
nvarrhar(
50))
インセンス
イント
@TempTable (UserID,UserName)
values (
1,
'
a.
')
インセンス
イント
@TempTable (UserID,UserName)
values (
2,
'
b
')
プロジェクト UserID,UserName
from
@TempTable
FOR XML PATH
このスクリプトを実行すると、次のような結果が発生します。
<>
row
>
<>
UserID
>1
UserID
>
<>
UserName
>a
UserName
>
row
>
<>
row
>
<>
UserID
>2
UserID
>
<>
UserName
>b
UserName
>
row
>
from
@TempTable
FOR XML PATH(
'
lzy
')
上記のスクリプトを再実行すると、次のような結果が発生します。
<>
lzy
>
<>
UserID
>1
UserID
>
<>
UserName
>a
UserName
>
lzy
>
<>
lzy
>
<>
UserID
>2
UserID
>
<>
UserName
>b
UserName
>
lzy
>
from
@TempTable
FOR XML PATH(
')
上記のスクリプトを実行すると結果が生成されます。
<>
UserID
>1
UserID
>
<>
UserName
>a
UserName
>
<>
UserID
>2
UserID
>
<>
UserName
>b
UserName
>
上位ノードは表示されません。PATHモードでは、列名または列の別名がXPS表現として扱われています。つまり、列の名前です。列名と別名を指定しないように大胆に試してみてはいかがですか?
プロジェクト
CAST(UserID
AS
varrhar
+
',UserName
+
''
from
@TempTable
FOR XML PATH(
')
上の文を実行すると結果が生成されます。
1 a 2 b
すべてのデータが1行生成されていて、まだ文字が接続されていません。このようなデータはみんなのために役に立たないかもしれません。また、変更してもいいです。
プロジェクト
CAST(UserID
AS
varrhar
+
'
を選択します。
',UserName
+
'
'
;
'
from
@TempTable
FOR XML PATH(
')
結果を生成
1,a2,b;
分かりましたよね。パラメータを制御することによって、自分の欲しい結果を生成することができます。例えば、
プロジェクト
'
{
'
+
CAST(UserID
AS
varrhar
+
'
を選択します。
'
'
「
'
+UserName
+
'
「
'
'
)
'
from
@TempTable
FOR XML PATH(
')
結果を生成
{1、“a”}{2、“b”}
他のフォーマットを生成することもできます。自分の好みに合わせて組み合わせられます。
以下はデータ統計の応用です。以下の例を通してもっと多くの応用を考えてください。
DECLARE
@T 1
テーブル(UserID
要点 , UserName
nvarrhar(
50)、CityName
nvarrhar(
50))
インセンス
イント
@T 1 (UserID,UserName,CityName)
values (
1,
'
a.
'
'
上海
')
インセンス
イント
@T 1 (UserID,UserName,CityName)
values (
2,
'
b
'
'
北京
')
インセンス
イント
@T 1 (UserID,UserName,CityName)
values (
3,
'
c
'
'
上海
')
インセンス
イント
@T 1 (UserID,UserName,CityName)
values (
4,
'
d
'
'
北京
')
インセンス
イント
@T 1 (UserID,UserName,CityName)
values (
5,
'
e
'
'
上海
')
SELECT B.CityName
LEFT(UserList)
LEN(UserList)
を選択します。
1)
FROM (
SELECT CityName
(
SELECT UserName
+
'
を選択します。
'
FROM
@T 1
WHERE CityName
=A.CityName
FOR XML PATH(
')
AS UserList
FROM
@T 1 A
GROUTP
BY。 CityName
) B
結果(各都市のユーザ名)
北京b,d上海a,c,e
http://msdn.microsoft.com/zh-cn/library/ms178107.aspx
【転載】http://www.cnblogs.com/yanghaibo/archive/2010/06/04/1751405.html
SQL Serverでは、FOR XML PATH文を利用してクエリのデータをXMLデータを生成することができます。以下はそのいくつかのアプリケーションの例です。
DECLARE
@TempTable
テーブル(UserID
要点 , UserName
nvarrhar(
50))
インセンス
イント
@TempTable (UserID,UserName)
values (
1,
'
a.
')
インセンス
イント
@TempTable (UserID,UserName)
values (
2,
'
b
')
プロジェクト UserID,UserName
from
@TempTable
FOR XML PATH
このスクリプトを実行すると、次のような結果が発生します。
<>
row
>
<>
UserID
>1
UserID
>
<>
UserName
>a
UserName
>
row
>
<>
row
>
<>
UserID
>2
UserID
>
<>
UserName
>b
UserName
>
row
>
, PATH :
プロジェクト UserID,UserName from
@TempTable
FOR XML PATH(
'
lzy
')
上記のスクリプトを再実行すると、次のような結果が発生します。
<>
lzy
>
<>
UserID
>1
UserID
>
<>
UserName
>a
UserName
>
lzy
>
<>
lzy
>
<>
UserID
>2
UserID
>
<>
UserName
>b
UserName
>
lzy
>
, PATH() , ( ) ?
プロジェクト UserID,UserName from
@TempTable
FOR XML PATH(
')
上記のスクリプトを実行すると結果が生成されます。
<>
UserID
>1
UserID
>
<>
UserName
>a
UserName
>
<>
UserID
>2
UserID
>
<>
UserName
>b
UserName
>
上位ノードは表示されません。PATHモードでは、列名または列の別名がXPS表現として扱われています。つまり、列の名前です。列名と別名を指定しないように大胆に試してみてはいかがですか?
プロジェクト
CAST(UserID
AS
varrhar
+
',UserName
+
''
from
@TempTable
FOR XML PATH(
')
上の文を実行すると結果が生成されます。
1 a 2 b
すべてのデータが1行生成されていて、まだ文字が接続されていません。このようなデータはみんなのために役に立たないかもしれません。また、変更してもいいです。
プロジェクト
CAST(UserID
AS
varrhar
+
'
を選択します。
',UserName
+
'
'
;
'
from
@TempTable
FOR XML PATH(
')
結果を生成
1,a2,b;
分かりましたよね。パラメータを制御することによって、自分の欲しい結果を生成することができます。例えば、
プロジェクト
'
{
'
+
CAST(UserID
AS
varrhar
+
'
を選択します。
'
'
「
'
+UserName
+
'
「
'
'
)
'
from
@TempTable
FOR XML PATH(
')
結果を生成
{1、“a”}{2、“b”}
他のフォーマットを生成することもできます。自分の好みに合わせて組み合わせられます。
以下はデータ統計の応用です。以下の例を通してもっと多くの応用を考えてください。
DECLARE
@T 1
テーブル(UserID
要点 , UserName
nvarrhar(
50)、CityName
nvarrhar(
50))
インセンス
イント
@T 1 (UserID,UserName,CityName)
values (
1,
'
a.
'
'
上海
')
インセンス
イント
@T 1 (UserID,UserName,CityName)
values (
2,
'
b
'
'
北京
')
インセンス
イント
@T 1 (UserID,UserName,CityName)
values (
3,
'
c
'
'
上海
')
インセンス
イント
@T 1 (UserID,UserName,CityName)
values (
4,
'
d
'
'
北京
')
インセンス
イント
@T 1 (UserID,UserName,CityName)
values (
5,
'
e
'
'
上海
')
SELECT B.CityName
LEFT(UserList)
LEN(UserList)
を選択します。
1)
FROM (
SELECT CityName
(
SELECT UserName
+
'
を選択します。
'
FROM
@T 1
WHERE CityName
=A.CityName
FOR XML PATH(
')
AS UserList
FROM
@T 1 A
GROUTP
BY。 CityName
) B
結果(各都市のユーザ名)
北京b,d上海a,c,e