|
今天看到一则处理xml的问题，以前也有写过xml的查询，下面是问题
?
declare @x xml,@y xml?
02set @x='<item>??
03<id>1 </id>??
04<title>姓名 </title>??
05<value> </value>??
06</item>??
07<item>??
08<id>2 </id>??
09<title>年龄 </title>??
10<value> </value>??
11</item>??
12'
13set @y='<item>??
14<id>1 </id>??
15<value>张三 </value>??
16</item>??
17<item>??
18<id>2 </id>??
19<value>20 </value>??
20</item>??
21'有连个变量如上所示：?
22现在要求得到下面的格式：?
23/*?
24<item>?
25??? <id>1</id>?
26??? <title>姓名 </title>?
27??? <value>张三 </value>?
28</item>?
29<item>?
30??? <id>2</id>?
31??? <title>年龄 </title>?
32??? <value>20 </value>?
33</item>?
34*/
随着标准化的执行，我相信以后sql里会有更多对xml的操作。
??? 下面是xml操作的答案：
?
?
01??????? select??
02??????? D.x.value('./id[1]','int') AS id,?
03??????? D.x.value('./title[1]','nvarchar(100)') AS title,?
04??????? D.x.value('./value[1]','nvarchar(100)') AS [value]?
05??? from @x.nodes('/*') as D(x)?
06??????
07;with t1?
08as(?
09??? select??
10??????? D.x.value('./id[1]',?
11??????? D.x.value('./title[1]',?
12??????? D.x.value('./value[1]','nvarchar(100)') AS [value]?
13??? from @x.nodes('/*') as D(x)),?
14t2?
15as(?
16??? select??
17??????? D.x.value('./id[1]',?
18??????? D.x.value('./value[1]','nvarchar(100)') AS [value]?
19??? from @y.nodes('/*') as D(x))?
20select a.id,title,b.[value]?
21from t1 as a?
22left join t2 as b on a.id = b.id?
23for xml path('item')
下面解读一下上面的sql：
WITH AS短语，也叫做子查询部分(subquery factoring)，可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION ALL的不同部分，作为提供数据的部分。
特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用WITH AS短语，则只要执行一遍即可。如果WITH AS短语所定义的表名被调用两次以上，则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里，如果只是被调用一次，则不会。而提示materialize则是强制将WITH AS短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。
然后就是两个对xml操作的函数：
如果希望将 XML 数据类型实例拆分为关系数据，nodes() 方法十分有用。它允许您标识将映射到新行的节点。
每一个 xml 数据类型实例都具有隐式提供的上下文节点。对于在列或变量中存储的 XML 实例来说，它是文档节点。文档节点是位于每个 xml 数据类型实例顶部的隐式节点。 nodes() 方法的结果是一个包含原始 XML 实例的逻辑副本的行集。在这些逻辑副本中，每个行示例的上下文节点都被设置成由查询表达式标识的节点之一。这样，后续的查询可以浏览与这些上下文节点相关的节点。 您可以从行集中检索多个值。例如，可以将 value() 方法应用于 nodes() 所返回的行集，从原始 XML 实例中检索多个值。请注意，当 value() 方法应用于 XML 实例时，它仅返回一个值。
以上是一点小小的分享，希望对大家的sql进步有帮助。
示例
A. 对 xml 类型的变量使用 nodes() 方法
在此示例中，现有一个包含 顶级元素和三个 子元素的 XML 文档。此查询使用 nodes() 方法为每个 元素设置单独的上下文节点。nodes() 方法返回包含三行的行集。每行都有一个原始 XML 的逻辑副本，其中每个上下文节点都标识原始文档中的一个不同的 元素。
然后，查询会从每行返回上下文节点：
?
DECLARE @x xml?
SET @x='<Root>
??? <row id="1"><name>Larry</name><oflw>some text</oflw></row>
??? <row id="2"><name>moe</name></row>
??? <row id="3" />
</Root>'
SELECT T.c.query('.') AS result
FROM?? @x.nodes('/Root/row') T(c)
go
?
?
??? 结果如下。在此示例中，查询方法返回上下文项及其内容：
?
?
?<row id="1"><name>Larry</name><oflw>some text</oflw></row>
?<row id="2"><name>moe</name></row>
?<row id="3"/>
?
??? 对上下文节点应用父级取值函数将返回所有三行的 <Root> 元素。
?
?
SELECT T.c.query('..') AS result
FROM?? @x.nodes('/Root/row') T(c)
go
???? 结果如下：
?
<Root>
??? <row id="1"><name>Larry</name><oflw>some text</oflw></row>
??? <row id="2"><name>moe</name></row>
??? <row id="3" />
</Root>
<Root>
??? <row id="1"><name>Larry</name><oflw>some text</oflw></row>
??? <row id="2"><name>moe</name></row>
??? <row id="3" />
</Root>
<Root>
??? <row id="1"><name>Larry</name><oflw>some text</oflw></row>
??? <row id="2"><name>moe</name></row>
??? <row id="3" />
</Root>
?
??? 下面的查询指定了绝对路径。对使用绝对路径表达式的上下文节点的查询，将从上下文节点的根节点开始进行。因此，您将收到由 nodes() 返回的每个上下文节点的全部三行。
?
?
?
SELECT T.c.query('/Root/row') AS result
FROM?? @x.nodes('/Root/row') T(c)
go
<row id="1"><name>Larry</name><oflw>some text</oflw></row>
<row id="2"><name>moe</name></row>
<row id="3" />
<row id="1"><name>Larry</name><oflw>some text</oflw></row>
<row id="2"><name>moe</name></row>
<row id="3" />
<row id="1"><name>Larry</name><oflw>some text</oflw></row>
<row id="2"><name>moe</name></row>
<row id="3" />
?
???? 请注意，由 xml 数据类型的 nodes() 方法返回的列无法直接使用。例如，下面的查询将返回错误：
?
...
SELECT T.c
FROM?? @x.nodes('/Root/row') T(c)
?
???? 在以下查询中，xml 数据类型的 value() 和 query() 方法应用到由 nodes() 方法返回的行集中。value() 方法返回上下文项 (<row>) 的 id 属性；query() 方法返回上下文项的 <name> 元素子树。
?
?
DECLARE @x xml?
SET @x='
<Root>
??? <row id="1"><name>Larry</name><oflw>some text</oflw></row>
??? <row id="2"><name>Joe</name></row>
??? <row id="3" />
</Root>
'
SELECT T.c.value('@id','int') as id,
?????? T.c.query('name') as NAME
FROM?? @x.nodes('/Root/row') T(c)
go
?
??? 结果如下：
?
?
?id? NAME
-----------------------
?1?? <name>Larry</name>
?2?? <name>Joe</name>
?3???
?
?
??? 请注意，结果包括行 ID 3 并且 <row> 元素不包含子 <name>。如果您希望对结果进行筛选，以便返回（或不返回）不带子 <name> 的行，就可以使用下列方法之一对其进行筛选：
??? 使用 nodes() 路径表达式（例如 /Root/row[name]）中的谓词。
??? 对行集使用 exist() 方法。
??? 使用 CROSS APPLY。
??? 使用 OUTER APPLY。
??? 以下查询对 nodes() 返回的行集指定 exist() 方法。如果上下文节点 (<row>) 包含子 <name>，则 exist() 方法返回 True。
?
?
DECLARE @x xml????????
SET @x='<Root>???????
??? <row id="1"><name>Larry</name><oflw>some text</oflw></row>???????
??? <row id="2"><name>Joe</name></row>???????
??? <row id="3" />???????
</Root>'???????
SELECT T1.rows.value() as id???????
FROM @x.nodes('/Root/row') T1(rows)???????
WHERE T1.rows.exist('name') = 1;???????
GO
? 此查询将返回两行：行 ID 分别为 1 和 2。
以下查询使用 OUTER APPLY。OUTER APPLY 将 nodes() 应用于 T1(行)中的每个行，并返回构成结果集的行，也会返回 NULL。因此，WHERE 子句用于筛选行并只检索 T2.names 列不为 NULL 的行。
?
?
DECLARE @x xml????????
SET @x='???????
<Root>???????
??? <row id="1"><name>Larry</name><oflw>some text</oflw></row>???????
??? <row id="2"><name>Joe</name></row>???????
??? <row id="3" />???????
</Root>'???????
SELECT T1.rows.value() 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() 应用于外部表 [T1（行）] 中的每一行，并且只返回当 nodes() 应用于 T1.rows 时构成结果集的行。在这种情况下，您不需要 WHERE 子句来测试 IS NOT NULL。
?
DECLARE @x xml????????
SET @x='<Root>???????
??? <row id="1"><name>Larry</name><oflw>some text</oflw></row>???????
??? <row id="2"><name>Joe</name></row>???????
??? <row id="3" />???????
</Root>'???????
SELECT T1.rows.value() as id???????
FROM @x.nodes('/Root/row') T1(rows)???????
CROSS APPLY T1.rows.nodes('./name') as T2(names)???????
GO??????
? 有关 CROSS APPLY 和 OUTER APPLY 的信息，请参阅使用 APPLY。
B. 针对 xml 类型的列指定 nodes() 方法
在此示例中使用自行车生产说明，并将其存储在 ProductModel 表的 Instructions xml 类型列中。有关详细信息，请参阅 AdventureWorks 数据库中的 xml 数据类型表示形式。
在以下示例中，nodes() 方法是针对 ProductModel 表中 xml 类型的 Instructions 列指定的。
nodes() 方法通过指定 /MI:root/MI:Location 路径将 元素设置为上下文节点。结果行集包括原始文档的逻辑副本，每个副本对应文档中的一个 节点，上下文节点设置为 元素。因此，nodes() 函数给出一组 上下文节点。
query() 方法针对此行集请求 self::node，因此将返回每行中的 元素。
在此示例中，查询在特定产品样式的生产说明文档中将每一个 元素都设置为上下文节点。您可以使用这些上下文节点来按照以下方式来检索值：
在每个 中查找 LocationID
在每个 中检索生产步骤( 子元素)
此查询使用 query() 方法返回上下文项，其中指定了 self::node() 的缩写语法 "."。
请注意以下方面：
nodes() 方法应用于 Instructions 列并返回一个行集 T (C)。此行集包含将 /root/Location 作为上下文项的原始生产说明文档的逻辑副本。
CROSS APPLY 将 nodes() 应用于 Instructions 表中的每一行，并只返回构成结果集的行。
?
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
?
?
??? 下面是部分结果：
?
?
<MI:Location LocationID="10"? ...>
?? <MI:step ... />
????? ...
</MI:Location>
<MI:Location LocationID="20"? ... >
??? <MI:step ... />
????? ...
</MI:Location>
...
?
???? 以下查询与先前的查询相似，但是它通过使用行集中的上下文节点，利用 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
?
?
??? 结果如下。为提高可读性，未显示命名空间。
?
?LId? result
?10? <MI:tool xmlns:MI="...">T-85A framing tool</MI:tool>
???? <MI:tool xmlns:MI="...">Trim Jig TJ-26</MI:tool>
???? <MI:tool xmlns:MI="...">router with a carbide tip 15</MI:tool>
????? <MI:tool xmlns:MI="...">Forming Tool FT-15</MI:tool>
?20
?30? <MI:tool xmlns:MI="...">standard debur tool</MI:tool>
?45? <MI:tool xmlns:MI="...">paint harness</MI:tool>
?50
?60
? C. 将 nodes() 应用于由其他 nodes() 方法返回的行集
以下代码查询 ProductModel 表的 Instructions 列中生产说明的 XML 文档。此查询返回包含产品样式 ID、生产位置和生产步骤的行集。
请注意以下方面：
首先，nodes() 方法应用于 Instructions 列并返回 T1(位置)行集。此行集包含将 /root/Location 作为上下文项的原始生产说明文档的逻辑副本。
其次，nodes() 应用于 T1(位置)行集并返回 T2(步骤)行集。此行集包含将 /root/Location 作为上下文项的原始生产说明文档的逻辑副本。
?
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?? <step ... />???????
7????? 10?? <step ... />???????
...???????
7????? 20?? <step ... />???????
7????? 20?? <step ... />???????
7????? 20?? <step ... />???????
...??????
?
???? 此查询两次声明 MI 前缀。此外，您可以使用 WITH XMLNAMESPACES 来声明一次前缀并在查询中使用它：
?
WITH XMLNAMESPACES (
?? 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions'? AS MI)
SELECT ProductModelID,
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() 方法应用于 T2（步骤）行集中的 XML，以便只检索至少使用一个生产工具的生产步骤。即：<step> 元素至少包含一个 <tool> 子元素。
?
?
WITH XMLNAMESPACES (
?? 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions' AS MI)
SELECT ProductModelID,?
?????? Locations.value( ?????? 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
?
?
原文出处：http://www.cnblogs.com/MR_ke/archive/2010/08/23/1806460.html
TechTarget中国原创内容，原文链接：http://www.searchdatabase.com.cn/showcontent_39469.htm
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
