sql 解析xml字段

差旅报销业务表结构

微信图片_20190428171053.png

出差人字段中数据存储为xml格式

<ArrayOfString xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><string>fff3b43d-3926-4068-94af-0d16aecb83e6</string><string>5876b64b-a1c0-459f-86c6-cf4025be3abd</string></ArrayOfString>

存储过程

-- =============================================
-- Author:      刘蕾
-- Create date: 2018-11-20
-- Description: <Description,,> 
 --传个objectid参数
DECLARE @ObjectID char(36)
SET @ObjectID='52235de4-2571-49fb-b971-d193f3674b4d'



DECLARE @ccry XML
SELECT @ccry = REPLACE( REPLACE(CONVERT(VARCHAR(MAX), ioc.xzccr),'<ArrayOfString xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">','<?xml version="1.0" ?>'),'</ArrayOfString>','')
FROM [I_ES_clbxd] ioc WHERE ioc.ObjectID  = @ObjectID



DECLARE @count INT 
SELECT @count = @ccry.value('count(//string)','int')



DECLARE @i INT 
SET @i = 1



DECLARE @UUID char(36)
SET @UUID=''



WHILE (@i<= @count)
BEGIN 

  select @UUID= @ccry.value('(string[position()=sql:variable("@i")])[1]','nvarchar(100)') 
 select @UUID
 SET @i = @i+1

END

输出结果

image.png