SQL 里解析 XML 格式 字段 信息

DECLARE @ItemMessage XML 
DECLARE @ItemTable TABLE(No NVARCHAR(300),zje NVARCHAR(300),yfje NVARCHAR(300),bcje NVARCHAR(300),URL NVARCHAR(300),Remark NVARCHAR(300))
SET @ItemMessage=N<List> 
 <item>
   
 <No>SM-JG-201303-0189</No>
  
  <zje>273,240</zje>
  
  <yfje>81,972</yfje>
   
 <bcje>134620</bcje>
  
  <URL>http://eipwf.aspirehld.com/Workflow/Page/SYS_HTPS/WF_HTPS/HT_HTPS_Default.aspx?MID=163755&amp;SID=3&amp;PID=310284</URL>
  
  <Remark /> 
 </item>
</List>
INSERT INTO @ItemTable 
( 
No, 
zje,
yfje,
bcje,
URL,
Remark
) 
SELECT T.c.value((No/text())[1],NVARCHAR(300)), 
T.c.value((zje/text())[1],NVARCHAR(300)) ,
T.c.value((yfje/text())[1],NVARCHAR(300)) ,
T.c.value((bcje/text())[1],NVARCHAR(300)) ,
T.c.value((URL/text())[1],NVARCHAR(300)),
T.c.value((Remark/text())[1],NVARCHAR(300))
FROM @ItemMessage.nodes(/List/item) AS T(c)
SELECT * FROM @ItemTable

上面的内容是在网上找到的一个,具体的含义是 先定义一个XML格式的ItemMessage,在解析的时候,先去除节点List和item,然后获取内部的节点字段,最后把这个插入一个临时的ItemTable里。

这是网上给的方法,到了具体使用的时候,我们可能并不是这么使用,现在提供一种简单的使用方式,假设我现在表里的字段XmlData存储的就是ItemMessage样式的内容,首先通过cast命令将XmlData转换成XML类型的格式,然后通过它的Value属性进行分解,可以看到和上面的差不多,/List/item/No为逐层分级,最后的as No是给这个获取到的字段付了一个名字。

cast(XmlData as XML).value((/List/item/No/text())[1],NVARCHAR(300)) as No

通过上面的这句话就可以很容易的解析出需要的内容了,解析的结果为

SM-JG-201303-0189


附:昨天领导让导数据,写的SQL,做个备份
select a.EBILLNO,
a.EMPNAME,
a.APPLYDATE,
b.HS_NAME,
replace(replace(a.SUMMARY,char(10), ‘‘),char(13),‘‘),
cast(c.XmlData as XML).value((/List/item/No/text())[1],NVARCHAR(300)) as No,
cast(c.XmlData as XML).value((/List/item/zje/text())[1],NVARCHAR(300)) as zje,
cast(c.XmlData as XML).value((/List/item/yfje/text())[1],NVARCHAR(300)) as yfje,
cast(c.XMLData as XML).value((/List/item/bcje/text())[1],NVARCHAR(300)) as bcje,
cast(c.XMLData as XML).value((/List/item/URL/text())[1],NVARCHAR(300)) as URL,
cast(c.XMLData as XML).value((/List/item/Remark/text())[1],NVARCHAR(300)) as BZ,
cast(d.XMLData as XML).value((/List/item/SKRXM/text())[1],NVARCHAR(300)) as SKRXM,
(http://……?sid=3&mid=7281&PID=+a.PID) as bxdljdz
from Ex_Bill as a 
left join Ex_System_Cfg as b on(a.BILLSYSTEMID=b.HS_ID and a.DATASYSTEMID=b.SYSTEM_NAME)
left join (select * from [192.168.3.23].Flow.dbo.RepeaingTable) as c on (c.Keyword=URL and c.ProcessID=a.PID)
left join (select * from [192.168.3.23].Flow.dbo.RepeaingTable) as d on (d.Keyword=FKXX_New and d.ProcessID=a.PID or d.Keyword=FKXX and d.ProcessID=a.PID)

where applyempid=‘zhongxun and a.EBILLNO is not null
and status>5 and status not in(200,100,7000)
and a.APPLYDATE>2011-01-01
and a.HT=
order by a.ID desc
replace(replace(a.SUMMARY,char(10), ‘‘),char(13),‘‘)

这句话的意思是去除a.SUMMARY里的换行,刚开始并没有加replace这个命令,后来导出Excel后,发现有换行,因此加入了这个命令。 

select * from [192.168.3.23].Flow.dbo.RepeaingTable

没有直接写表名,而加入了地址,是因为要联合查询的表不在同一个服务器下,查询多个服务器上的数据要先进行链接服务器的操作,这样才可以联合查询多个服务器。

链接服务器的方法是:先登录一个数据库的服务器,在Microsoft SQL Server Management Studio左侧的对象资源管理器里找到“服务器对象---链接服务器”,右键新建链接服务器。

SQL 里解析 XML 格式 字段 信息,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。