使用OpenXML [英] Working with OpenXML
本文介绍了使用OpenXML的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在将XML传递到SQL Server存储过程,并尝试将其存储在#temp
表中.
I am passing XML to SQL Server stored procedure and trying it to store in #temp
table.
存储过程如下:
EXEC sp_xml_preparedocument @i OUTPUT
,@XMLDOC
SELECT Id
,UserId
,ModifiedOn
,ModifiedBy
,ModifiedIp
,DetailId
INTO #temp
FROM OPENXML(@i, '/Root/Rec/Detail', 1) WITH (
id INT '../@Id'
,UserId INT '../@UserId'
,ModifiedOn DATETIME '../@ModifiedOn'
,ModifiedBy INT '../@ModifiedBy'
,ModifiedIp VARCHAR(15) '../@ModifiedIp'
,DetailId INT '@DetailId'
)
EXEC sp_xml_removedocument @i
select * from #temp
以及XML示例:
<root>
<rec id="I001" userid="316" modifiedon="9/10/2016 02:33:56 PM" modifiedby="316" modifiedip="::1" UserDetail="System.Collections.Generic.List`1[Dto.UserDetail]">
</rec>
</root>
当XML包含子节点(detail
)时,此SP将正常工作.如果我没有子节点-它将无法工作.你能指出我哪里出问题了吗?
When the XML contains child nodes (detail
), this SP will work fine. If I don't have child nodes - it will not work. Could you point out where I went wrong?
推荐答案
完全重写答案:
DECLARE @i int,
@XMLDOC VARCHAR(max)= "INPUT XML"
EXEC sp_xml_preparedocument @i OUTPUT
,@XMLDOC
SELECT *
INTO #temp
FROM (
SELECT m.Id,
m.UserId,
m.ModifiedOn,
m.ModifiedBy,
m.ModifiedIp,
COALESCE(det.DetailId, -1) as DetailId
FROM OPENXML(@i, '/root/rec', 1) WITH (
id INT '@availabilityid',
UserId INT '@userid',
ModifiedOn DATETIME '@modifiedon',
ModifiedBy INT '@modifiedby',
ModifiedIp VARCHAR(15) '@modifiedip'
) as m
LEFT JOIN (
SELECT Id,
DetailId
FROM OPENXML(@i, '/root/rec/userturnarounddetail', 1) WITH (
id INT '../@availabilityid',
DetailId INT '@availabilitydetailid'
)
) as det
ON m.id =det.id
) p
EXEC sp_xml_removedocument @i
select * from #temp
输出:
Id UserId ModifiedOn ModifiedBy ModifiedIp DetailId
113367 316 2016-09-11 11:01:09.000 316 ::1 300100
0 316 2016-09-11 11:01:11.000 316 ::1 -1
编辑
带有.nodes
方法的示例.从SQL Server 2008开始可用.
Example with .nodes
method. It is available since SQL Server 2008.
DECLARE @XMLDOC xml
SELECT @XMLDOC =
'<root>
<rec id="I001" userid="316" modifiedon="9/10/2016 02:33:56 PM" modifiedby="316" modifiedip="::1" UserDetail="System.Collections.Generic.List1[Dto.UserDetail]">
</rec>
</root>'
SELECT ISNULL(d.f.value('@id','nvarchar(15)'),d.f.value('../@id','nvarchar(15)')) as id,
ISNULL(d.f.value('@userid','int'),d.f.value('../@userid','int')) as UserId,
ISNULL(d.f.value('@modifiedon','datetime'),d.f.value('../@modifiedon','datetime')) as ModifiedOn,
ISNULL(d.f.value('@modifiedby','int'),d.f.value('../@modifiedby','int')) as ModifiedBy,
ISNULL(d.f.value('@modifiedip','nvarchar(15)'),d.f.value('../@modifiedip','nvarchar(15)')) as ModifiedIp,
d.f.value('@detailid','int') as DetailId
FROM @XMLDOC.nodes('//*//*') as d(f)
这篇关于使用OpenXML的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文