XML格式
<ArrayOfString xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<string>48a3a451-419c-4e99-a227-61ca98ac8991</string>
<string>2da29c06-4fad-4c14-8524-1b4cd20f822a</string>
</ArrayOfString>
将string 拆分成多条记录,使用以下SQL可实现
with StaffOrg as
(
--查询编制对应的组织
select
scpes.content.value('.[1]','nvarchar(36)') as scope ,
OT_OrgPostStaff.ObjectID
from OT_OrgPostStaff
cross apply
OT_OrgPostStaff.OUScope.nodes('/ArrayOfString/string') as scpes(content)
)
select * from StaffOrg