===================================================--建立xml数据类型测试表create table XMLTest( ModleID int ,ModleName nvarchar(200) ,ColXML xml)insert into XMLTest(ModleID,ModleName,ColXML)select 2,'testname2','<Root> <row id="1"> <name>Larry</name> <oflw>some text</oflw> </row> <row id="2"> <name>Joe</name> </row> <row id="3" /></Root>'union all select 3,'testname3','<Root> <row id="13"> <name>Larry34</name> <oflw>some text</oflw> </row> <row id="24"> <name>Joe434</name> </row> <row id="43"> <name>name443</name> </row> <row id="45"> <name>8925132</name> </row> <row id="76"> <name>rewwe</name> </row></Root>'
--测试select a.modleid,T.rows.query('.') XMLQuery,t.rows.query('.').query('data(//name)') [Name],t.rows.query('.').query('data(//oflw)') [Oflw]from XMLTest Across apply ColXML.nodes('/Root/row') T(rows)
--查询单行的xml某个固定值,注意区分大小写select *,ColXML.value('(/Root/row/@id)[1]','varchar(20)'),ColXML.value('(/Root/row/@id)[2]','varchar(20)')from xmltest
--------------------
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>'--用根数组进行查询xml数据值select @x.value('(/Root[1]/row[2]/@id)','varchar(20)')--通过路径进行循环检索xml数据值,根路径的()是少不了.select @x.value('(/Root/row/@id)[1]','varchar(20)')--查询某个结点值select @x.query('data(//name)'),@x.query('data(//oflw)')--nodes用法select *,colxml.query('data(//name)'),colxml.query('data(//oflw)') from xmltest
如果希望将 XML 数据类型实例拆分为关系数据，nodes() 方法十分有用。它允许您标识将映射到新行的节点。每一个 xml 数据类型实例都具有隐式提供的上下文节点。对于在列或变量中存储的 XML 实例来说，它是文档节点。文档节点是位于每个 xml 数据类型实例顶部的隐式节点。 nodes() 方法的结果是一个包含原始 XML 实例的逻辑副本的行集。在这些逻辑副本中，每个行示例的上下文节点都被设置成由查询表达式标识的节点之一。这样，后续的查询可以浏览与这些上下文节点相关的节点。
在此示例中，现有一个包含 <Root> 顶级元素和三个 <row> 子元素的 XML 文档。此查询使用 nodes() 方法为每个 <row> 元素设置单独的上下文节点。nodes() 方法返回包含三行的行集。每行都有一个原始 XML 的逻辑副本，其中每个上下文节点都标识原始文档中的一个不同的 <row> 元素。然后，查询会从每行返回上下文节点：
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 resultFROM @x.nodes('/Root/row') T(c)go
SELECT *--XSN.nameFROM sys.xml_schema_collections XSC JOIN sys.xml_schema_namespaces XSN ON (XSC.xml_collection_id = XSN.xml_collection_id)
--生成xml数据,显示路径,以指定的root路径进行..select * from XMLTestwhere ModleID=3 for xml path,root(N'ColXML')
select * from XMLTestfor xml path,root(N'ModleName')
