XML的最基本的用法[SQL 2005]

本文链接：https://blog.csdn.net/Hopewell_Go/article/details/1503899

本文介绍如何在SQL中使用XML数据类型，包括创建XML测试表、插入XML数据、查询XML值及利用nodes()方法将XML数据拆分为关系数据。还展示了如何生成XML数据并指定其路径。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

===================================================
--建立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 A
cross 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 result
FROM   @x.nodes('/Root/row') T(c)
go

SELECT *--XSN.name
FROM sys.xml_schema_collections XSC JOIN sys.xml_schema_namespaces XSN
ON (XSC.xml_collection_id = XSN.xml_collection_id)

--生成xml数据,显示路径,以指定的root路径进行..
select * from XMLTest
where ModleID=3
for xml path,root(N'ColXML')

select * from XMLTest
for xml path,root(N'ModleName')