SqlServer2005處理xml格式

declare @x xml,@y xml
set @x=' 
1  
 
  
 
 
2  
 
  
 
'
set @y=' 
1  
張三  
 
 
2  
20  
 
'有連個變量如上所示：
現(xiàn)在要求得到下面的格式：
/*

 1
 
 張三 


 2
 
 20 

*/

select 
 D.x.value('./id[1]','int') AS id,
 D.x.value('./title[1]','nvarchar(100)') AS title,
 D.x.value('./value[1]','nvarchar(100)') AS [value]
 from @x.nodes('/*') as D(x)
 
;with t1
as(
 select 
 D.x.value('./id[1]','int') AS id,
 D.x.value('./title[1]','nvarchar(100)') AS title,
 D.x.value('./value[1]','nvarchar(100)') AS [value]
 from @x.nodes('/*') as D(x)),
t2
as(
 select 
 D.x.value('./id[1]','int') AS id,
 D.x.value('./value[1]','nvarchar(100)') AS [value]
 from @y.nodes('/*') as D(x))
select a.id,title,b.[value]
from t1 as a
left join t2 as b on a.id = b.id
for xml path('item')

示例
A. 對 xml 類型的變量使用 nodes() 方法
在此示例中，現(xiàn)有一個包含  頂級元素和三個  子元素的 XML 文檔。此查詢使用 nodes() 方法為每個  元素設(shè)置單獨的上下文節(jié)點。nodes() 方法返回包含三行的行集。每行都有一個原始 XML 的邏輯副本，其中每個上下文節(jié)點都標識原始文檔中的一個不同的  元素。

然后，查詢會從每行返回上下文節(jié)點： 

 
DECLARE @x xml 
SET @x='
 Larrysome text
 moe
 
'
SELECT T.c.query('.') AS result
FROM @x.nodes('/Root/row') T(c)
go
 

結(jié)果如下。在此示例中，查詢方法返回上下文項及其內(nèi)容：

 
 Larrysome text
 moe
 
 

對上下文節(jié)點應(yīng)用父級取值函數(shù)將返回所有三行的  元素。

 
SELECT T.c.query('..') AS result
FROM @x.nodes('/Root/row') T(c)
go
 

結(jié)果如下：

 

 Larrysome text
 moe
 


 Larrysome text
 moe
 


 Larrysome text
 moe
 

 

下面的查詢指定了絕對路徑。對使用絕對路徑表達式的上下文節(jié)點的查詢，將從上下文節(jié)點的根節(jié)點開始進行。因此，您將收到由 nodes() 返回的每個上下文節(jié)點的全部三行。

 
SELECT T.c.query('/Root/row') AS result
FROM @x.nodes('/Root/row') T(c)
go
Larrysome text
moe

Larrysome text
moe

Larrysome text
moe

 

請注意，由 xml 數(shù)據(jù)類型的 nodes() 方法返回的列無法直接使用。例如，下面的查詢將返回錯誤：

 
...
SELECT T.c
FROM @x.nodes('/Root/row') T(c)
 

在以下查詢中，xml 數(shù)據(jù)類型的 value() 和 query() 方法應(yīng)用到由 nodes() 方法返回的行集中。value() 方法返回上下文項 () 的 id 屬性；query() 方法返回上下文項的  元素子樹。 

 
DECLARE @x xml 
SET @x='

 Larrysome text
 Joe
 

'
SELECT T.c.value('@id','int') as id,
 T.c.query('name') as NAME
FROM @x.nodes('/Root/row') T(c)
go
 

結(jié)果如下：

 
 id NAME
-----------------------
 1 Larry
 2 Joe
 3 
 

請注意，結(jié)果包括行 ID 3 并且  元素不包含子 。如果您希望對結(jié)果進行篩選，以便返回（或不返回）不帶子  的行，就可以使用下列方法之一對其進行篩選： 

使用 nodes() 路徑表達式（例如 /Root/row[name]）中的謂詞。


對行集使用 exist() 方法。


使用 CROSS APPLY。


使用 OUTER APPLY。


以下查詢對 nodes() 返回的行集指定 exist() 方法。如果上下文節(jié)點 () 包含子 ，則 exist() 方法返回 True。

 
DECLARE @x xml 
SET @x=' 
 Larrysome text 
 Joe 
  
' 
SELECT T1.rows.value('@id','int') as id 
FROM @x.nodes('/Root/row') T1(rows) 
WHERE T1.rows.exist('name') = 1; 
GO
 

此查詢將返回兩行：行 ID 分別為 1 和 2。 

以下查詢使用 OUTER APPLY。OUTER APPLY 將 nodes() 應(yīng)用于 T1（行）中的每個行，并返回構(gòu)成結(jié)果集的行，也會返回 NULL。因此，WHERE 子句用于篩選行并只檢索 T2.names 列不為 NULL 的行。

 
DECLARE @x xml 
SET @x=' 
 
 Larrysome text 
 Joe 
  
' 
SELECT T1.rows.value('@id','int') as id 
FROM @x.nodes('/Root/row') T1(rows) 
OUTER APPLY T1.rows.nodes('./name') as T2(names) 
WHERE T2.names is not null 
GO 
 

以下查詢使用 CROSS APPLY。CROSS APPLY 將 nodes() 應(yīng)用于外部表 [T1（行）] 中的每一行，并且只返回當 nodes() 應(yīng)用于 T1.rows 時構(gòu)成結(jié)果集的行。在這種情況下，您不需要 WHERE 子句來測試 IS NOT NULL。

 
DECLARE @x xml 
SET @x=' 
 Larrysome text 
 Joe 
  
' 
SELECT T1.rows.value('@id','int') as id 
FROM @x.nodes('/Root/row') T1(rows) 
CROSS APPLY T1.rows.nodes('./name') as T2(names) 
GO 
 

有關(guān) CROSS APPLY 和 OUTER APPLY 的信息，請參閱使用 APPLY。

B. 針對 xml 類型的列指定 nodes() 方法
在此示例中使用自行車生產(chǎn)說明，并將其存儲在 ProductModel 表的 Instructions xml 類型列中。有關(guān)詳細信息，請參閱 AdventureWorks 數(shù)據(jù)庫中的 xml 數(shù)據(jù)類型表示形式。 

在以下示例中，nodes() 方法是針對 ProductModel 表中 xml 類型的 Instructions 列指定的。 

nodes() 方法通過指定 /MI:root/MI:Location 路徑將  元素設(shè)置為上下文節(jié)點。結(jié)果行集包括原始文檔的邏輯副本，每個副本對應(yīng)文檔中的一個  節(jié)點，上下文節(jié)點設(shè)置為  元素。因此，nodes() 函數(shù)給出一組  上下文節(jié)點。 

query() 方法針對此行集請求 self::node，因此將返回每行中的  元素。 

在此示例中，查詢在特定產(chǎn)品樣式的生產(chǎn)說明文檔中將每一個  元素都設(shè)置為上下文節(jié)點。您可以使用這些上下文節(jié)點來按照以下方式來檢索值： 

在每個  中查找 LocationID 


在每個  中檢索生產(chǎn)步驟（ 子元素） 


此查詢使用 query() 方法返回上下文項，其中指定了 self::node() 的縮寫語法 "."。

請注意以下方面： 

nodes() 方法應(yīng)用于 Instructions 列并返回一個行集 T (C)。此行集包含將 /root/Location 作為上下文項的原始生產(chǎn)說明文檔的邏輯副本。


CROSS APPLY 將 nodes() 應(yīng)用于 Instructions 表中的每一行，并只返回構(gòu)成結(jié)果集的行。 

 
SELECT C.query('.') as result
FROM Production.ProductModel
CROSS APPLY Instructions.nodes('
declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
/MI:root/MI:Location') as T(C)
WHERE ProductModelID=7
 
下面是部分結(jié)果： 

 

 
 ...


 
 ...

...
 

以下查詢與先前的查詢相似，但是它通過使用行集中的上下文節(jié)點，利用 value() 和 query() 檢索一組值。對于每個位置，SELECT 子句都檢索在該位置使用的位置 ID 和工具。 

 
SELECT C.value('@LocationID','int') as LId,
 C.query('declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
   MI:step/MI:tool') as result
FROM Production.ProductModel
CROSS APPLY Instructions.nodes('
declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
/MI:root/MI:Location') as T(C)
WHERE ProductModelID=7
 

結(jié)果如下。為提高可讀性，未顯示命名空間。

 
 LId result
 10 T-85A framing tool
 Trim Jig TJ-26
 router with a carbide tip 15
 Forming Tool FT-15
 20
 30 standard debur tool
 45 paint harness
 50
 60
 

C. 將 nodes() 應(yīng)用于由其他 nodes() 方法返回的行集
以下代碼查詢 ProductModel 表的 Instructions 列中生產(chǎn)說明的 XML 文檔。此查詢返回包含產(chǎn)品樣式 ID、生產(chǎn)位置和生產(chǎn)步驟的行集。 

請注意以下方面： 

首先，nodes() 方法應(yīng)用于 Instructions 列并返回 T1（位置）行集。此行集包含將 /root/Location 作為上下文項的原始生產(chǎn)說明文檔的邏輯副本。


其次，nodes() 應(yīng)用于 T1（位置）行集并返回 T2（步驟）行集。此行集包含將 /root/Location 作為上下文項的原始生產(chǎn)說明文檔的邏輯副本。


 
SELECT ProductModelID, Locations.value('./@LocationID','int') as LocID,
steps.query('.') as Step 
FROM Production.ProductModel 
CROSS APPLY Instructions.nodes(' 
declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"; 
/MI:root/MI:Location') as T1(Locations) 
CROSS APPLY T1.Locations.nodes(' 
declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"; 
./MI:step ') as T2(steps) 
WHERE ProductModelID=7 
go 
-- result 
ProductModelID LocID Step 
---------------------------- 
7 10  
7 10  
... 
7 20  
7 20  
7 20  
... 
 

此查詢兩次聲明 MI 前綴。此外，您可以使用 WITH XMLNAMESPACES 來聲明一次前綴并在查詢中使用它：

 
WITH XMLNAMESPACES (
 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions' AS MI)

SELECT ProductModelID, Locations.value('./@LocationID','int') as LocID,
steps.query('.') as Step 
FROM Production.ProductModel 
CROSS APPLY Instructions.nodes(' 
/MI:root/MI:Location') as T1(Locations) 
CROSS APPLY T1.Locations.nodes(' 
./MI:step ') as T2(steps) 
WHERE ProductModelID=7 
go 
 

以下查詢與先前的查詢相似，但是它將 exist() 方法應(yīng)用于 T2（步驟）行集中的 XML，以便只檢索至少使用一個生產(chǎn)工具的生產(chǎn)步驟。即： 元素至少包含一個  子元素。

 
WITH XMLNAMESPACES (
 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions' AS MI)

SELECT ProductModelID, 
 Locations.value('./@LocationID','int') as LocID,
 steps.query('.') as Steps
FROM Production.ProductModel
CROSS APPLY Instructions.nodes('/MI:root/MI:Location') as T1(Locations)
CROSS APPLY T1.Locations.nodes('./MI:step') as T2(steps)
WHERE ProductModelID=7
AND steps.exist('./MI:tool') = 1
Go