OPENXML解析sp_xml_preparedocument获取的XML句柄
1、sp_xml_preparedocument与sp_xml_removedocument (1)sp_xml_preparedocument返回可用于访问新创建的内部表示形式的 XML 文档的句柄。 sp_xml_preparedocument hdoc OUTPUT [,xmltext ] [,xpath_namespaces ] hdoc [?xmltext?] [?xpath_namespaces?] (2)sp_xml_removedocument删除文档句柄指定的 XML 文档的内部表示形式并使该文档句柄无效。 sp_xml_removedocument hdoc hdoc ? 2、OPENXML OPENXML用来解析sp_xml_preparedocument获取的XML句柄。 OPENXML( idoc int [ in],rowpattern nvarchar [ in ],[ flags byte [ in ] ] ) [ WITH ( SchemaDeclaration | TableName ) ] idoc rowpattern flag
?
SchemaDeclaration ?
? ?
? ?
? ?
? TableName
?
3、实例 (1)XML内容 DECLARE @xmlResult XML --XML句柄 DECLARE @docHandle INT SET @xmlResult=N‘<ImgLabelPointRootXml> <Imgs> <ImgLabelPointModel photoid="105BDF53-1DF2-4C7D-B058-4CDB9A8BA152" isvalid="1"> <photoid>105BDF53-1DF2-4C7D-B058-4CDB9A8BA152</photoid> <isvalid>1</isvalid> <status>1</status> <height>400.123</height> <Width>299.84</Width> <InitHeight>2580.00</InitHeight> <CheckDetailId p4:nil="true" xmlns:p4="http://www.w3.org/2001/XMLSchema-instance" /> </ImgLabelPointModel> <ImgLabelPointModel photoid="AA001EAC-8AAC-4D08-98BC-82F27C0CBD73" isvalid="1"> <photoid>AA001EAC-8AAC-4D08-98BC-82F27C0CBD73</photoid> <isvalid>1</isvalid> <status>0</status> <height p4:nil="true" xmlns:p4="http://www.w3.org/2001/XMLSchema-instance" /> <Width p4:nil="true" xmlns:p4="http://www.w3.org/2001/XMLSchema-instance" /> <InitHeight p4:nil="true" xmlns:p4="http://www.w3.org/2001/XMLSchema-instance" /> <CheckDetailId>100012004</CheckDetailId> </ImgLabelPointModel> </Imgs> <Points> <ImgLabelPointBoxModel skucode="600152" top="210.28"> <skucode>600152</skucode> <top>210.28</top> <left>143.39</left> <CheckDetailId p4:nil="true" xmlns:p4="http://www.w3.org/2001/XMLSchema-instance" /> <SameGuid>5B07D255-E0FA-4A00-80B7-AA4BD4E43AD0</SameGuid> <photoid>105BDF53-1DF2-4C7D-B058-4CDB9A8BA152</photoid> </ImgLabelPointBoxModel> <ImgLabelPointBoxModel skucode="600153" top="210.75"> <skucode>600153</skucode> <top>210.75</top> <left>103.45</left> <CheckDetailId p4:nil="true" xmlns:p4="http://www.w3.org/2001/XMLSchema-instance" /> <SameGuid>5D809A5D-5D11-4689-A96E-68891D63D8F0</SameGuid> <photoid>105BDF53-1DF2-4C7D-B058-4CDB9A8BA152</photoid> </ImgLabelPointBoxModel> </Points> </ImgLabelPointRootXml>‘ 表结构 CREATE TABLE #tempImgs( photoid VARCHAR(50),isvalid INT,status INT,height DECIMAL(10,6),--Width DECIMAL(10,--InitHeight DECIMAL(10,CheckDetailId INT ) CREATE TABLE #tempPoints( skucode INT,[top] DECIMAL(10,[left] DECIMAL(10,--CheckDetailId INT,SameGuid VARCHAR(50),photoid VARCHAR(50) ) (2)按元素获取 EXEC sp_xml_preparedocument @docHandle OUTPUT,@xmlResult INSERT INTO #tempPoints SELECT * FROM OPENXML(@docHandle,‘/ImgLabelPointRootXml/Points/ImgLabelPointBoxModel‘,2) WITH #tempPoints SELECT * FROM #tempPoints (3)按属性获取 EXEC sp_xml_preparedocument @docHandle OUTPUT,1) WITH #tempPoints (4)指定XPath混合获取 EXEC sp_xml_preparedocument @docHandle OUTPUT,1) WITH ( skucode INT,6) ‘left‘,SameGuid VARCHAR(50) ‘SameGuid‘,photoid VARCHAR(50) ‘photoid‘ ) 格式如下 属性:‘@ProductID‘?‘../@OrderDate‘ 元素:‘OrderID‘ ? ? 4、注意事项 ?(1)转换格式失败需要配合SchemaDeclaration使用,如?Error converting data type nvarchar to numeric in XML data retrieving ----类型转换失败的问题先通过with指定兼容的类型,select再转换 EXEC sp_xml_preparedocument @docHandle OUTPUT,@xmlResult INSERT INTO #tempImgs SELECT photoid,isvalid,status,CASE WHEN ISNUMERIC(height)=1 THEN CAST(height AS DECIMAL(10,6)) ELSE 0 END,CheckDetailId FROM OPENXML(@docHandle,‘/ImgLabelPointRootXml/Imgs/ImgLabelPointModel‘,2) WITH (photoid VARCHAR(50),height varchar(20),CheckDetailId INT )
参考: sp_xml_preparedocument (Transact-SQL) https://docs.microsoft.com/zh-cn/sql/relational-databases/system-stored-procedures/sp-xml-preparedocument-transact-sql?view=sql-server-2017? OPENXML (Transact-SQL) https://docs.microsoft.com/zh-cn/sql/t-sql/functions/openxml-transact-sql?view=sql-server-2017 使用sp_xml_preparedocument处理XML文档 https://www.cnblogs.com/oec2003/archive/2011/07/23/2742014.html (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |