今天，在csdn上看到一则处理xml的问题，以前也有写过xml的查询，下面是问题 declare @x xml,@y xmlset @x='item id1 /id title姓名 /title value /value /item item id2 /id title年龄 /title value /value /item 'set @y='item id1 /id value张三 /value /i
    今天，在csdn上看到一则处理xml的问题，以前也有写过xml的查询，下面是问题
declare @x xml,@y xmlset @x='1 姓名 2 年龄 'set @y='1 张三 2 20 '有连个变量如上所示：现在要求得到下面的格式：/*1姓名 张三 2年龄 20 */
下面是xml操作的答案：
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 t1as( 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)),t2as( 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 aleft join t2 as b on a.id = b.idfor xml path('item')
随着标准化的执行，我相信以后sql里会有更多对xml的操作。
    下面解读一下上面的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 实例时，它仅返回一个值。
示例a. 对 xml 类型的变量使用 nodes() 方法在此示例中，现有一个包含 顶级元素和三个 子元素的 xml 文档。此查询使用 nodes() 方法为每个 元素设置单独的上下文节点。nodes() 方法返回包含三行的行集。每行都有一个原始 xml 的逻辑副本，其中每个上下文节点都标识原始文档中的一个不同的 元素。然后，查询会从每行返回上下文节点： 复制代码 declare @x xml set @x='larrysome textmoe'select t.c.query('.') as resultfrom @x.nodes('/root/row') t(c)go 结果如下。在此示例中，查询方法返回上下文项及其内容： 复制代码 larrysome textmoe 对上下文节点应用父级取值函数将返回所有三行的 元素。 复制代码 select t.c.query('..') as resultfrom @x.nodes('/root/row') t(c)go 结果如下： 复制代码 larrysome textmoelarrysome textmoelarrysome textmoe 下面的查询指定了绝对路径。对使用绝对路径表达式的上下文节点的查询，将从上下文节点的根节点开始进行。因此，您将收到由 nodes() 返回的每个上下文节点的全部三行。 复制代码 select t.c.query('/root/row') as resultfrom @x.nodes('/root/row') t(c)golarrysome textmoelarrysome textmoelarrysome textmoe 请注意，由 xml 数据类型的 nodes() 方法返回的列无法直接使用。例如，下面的查询将返回错误： 复制代码 ...select t.cfrom @x.nodes('/root/row') t(c) 在以下查询中，xml 数据类型的 value() 和 query() 方法应用到由 nodes() 方法返回的行集中。value() 方法返回上下文项 () 的 id 属性；query() 方法返回上下文项的 元素子树。 复制代码 declare @x xml set @x='larrysome textjoe'select t.c.value('@id','int') as id, t.c.query('name') as namefrom @x.nodes('/root/row') t(c)go 结果如下： 复制代码 id name----------------------- 1 larry 2 joe 3 请注意，结果包括行 id 3 并且 元素不包含子 。如果您希望对结果进行筛选，以便返回（或不返回）不带子 的行，就可以使用下列方法之一对其进行筛选： 使用 nodes() 路径表达式（例如 /root/row[name]）中的谓词。对行集使用 exist() 方法。使用 cross apply。使用 outer apply。以下查询对 nodes() 返回的行集指定 exist() 方法。如果上下文节点 () 包含子 ，则 exist() 方法返回 true。 复制代码 declare @x xml set @x='larrysome textjoe' 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() 应用于 t1（行）中的每个行，并返回构成结果集的行，也会返回 null。因此，where 子句用于筛选行并只检索 t2.names 列不为 null 的行。 复制代码 declare @x xml set @x=' larrysome textjoe' 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() 应用于外部表 [t1（行）] 中的每一行，并且只返回当 nodes() 应用于 t1.rows 时构成结果集的行。在这种情况下，您不需要 where 子句来测试 is not null。 复制代码 declare @x xml set @x='larrysome textjoe' 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 有关 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 resultfrom production.productmodelcross 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 下面是部分结果： 复制代码 ... ...... 以下查询与先前的查询相似，但是它通过使用行集中的上下文节点，利用 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 resultfrom production.productmodelcross 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 t-85a framing tooltrim jig tj-26router with a carbide tip 15forming tool ft-15 20 30 standard debur tool 45 paint harness 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 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() 方法应用于 t2（步骤）行集中的 xml，以便只检索至少使用一个生产工具的生产步骤。即： 元素至少包含一个 子元素。 复制代码 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 stepsfrom production.productmodelcross apply instructions.nodes('/mi:root/mi:location') as t1(locations)cross apply t1.locations.nodes('./mi:step') as t2(steps)where productmodelid=7and steps.exist('./mi:tool') = 1go
以上是一点小小的分享，希望对大家的sql进步有帮助。