首页 > 代码库 > SQL 里解析 XML 格式 字段 信息

SQL 里解析 XML 格式 字段 信息

2024-07-11 14:53:38   216人阅读

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 bxdljdzfrom 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 nulland 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左侧的对象资源管理器里找到“服务器对象---链接服务器”，右键新建链接服务器。


联系
我们