请教一段sp_xml_preparedocument
USE [Takewin.GMCC.RMC]
GO
DECLARE@ListEngineParamId nvarchar(max)
set @ListEngineParamId = N'<?xml version="1.0" encoding="utf-16"?>
<ArrayOfString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<string>0501</string>
<string>0502</string>
<string>0503</string>
</ArrayOfString>'
declare @hdoc int
exec sp_xml_preparedocument @hdoc output, @ListEngineParamId
Select string
From openxml(@hdoc,'/ArrayOfString',2)
with (string char(4))
exec sp_xml_removedocument @hdoc
DECLARE@ListEngineParamId nvarchar(max)
set @ListEngineParamId = N'<?xml version="1.0" encoding="utf-16"?>
<ArrayOfString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<string>0501</string>
<string>0502</string>
<string>0503</string>
</ArrayOfString>'
declare @hdoc int
exec sp_xml_preparedocument @hdoc output, @ListEngineParamId
Select string
From openxml(@hdoc,'/ArrayOfString/string',2)
WITH (string CHAR(4) '.')
exec sp_xml_removedocument @hdoc
/*
string
0501
0502
0503
*/
declare @ListEngineParamId nvarchar(max)
set @ListEngineParamId=N'<?xml version="1.0" encoding="utf-16"?>
<ArrayOfString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<string>0501</string>
<string>0502</string>
<string>0503</string>
</ArrayOfString>'
select o.value('.','char(4)') 'str'
from (select cast(@ListEngineParamId as xml) 'x') t
cross apply x.nodes('/ArrayOfString/string') x(o)
/*
str
----
0501
0502
0503
(3 row(s) affected)
*/