Sqlserver XML OpenXML没得到结果集记录解决方法

2013-01-25

SqlserverXML OpenXML没得到结果集记录如下为什么 openxml没得到结果集记录？请大侠帮忙看看DECLARE @idoc

Sqlserver XML OpenXML没得到结果集记录
如下为什么 openxml没得到结果集记录？请大侠帮忙看看

DECLARE @idoc int
DECLARE @doc XML
set @doc ='<MGMsg xmlns="http://www.onlinetest.com/test" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.onlinetest.com/ttest.XSD">
<MsgHdr>
  <MsgId>MG-BB02</MsgId>
  <MsgMajVsn>1</MsgMajVsn>
  <MsgMinVsn>0</MsgMinVsn>
  <CrtnDm>16/10/12 14:04</CrtnDm>
  <LastProcessedDm>16/10/12 14:04</LastProcessedDm>
  <MsgOrgnatorAppl>SYS</MsgOrgnatorAppl>
  <MsgGenAddr>PEK-HH</MsgGenAddr>
  <LastProcessedBy>SYS</LastProcessedBy>
  </MsgHdr>
</MGMsg>'
EXEC sp_xml_preparedocument @idoc OUTPUT,@doc
print @idoc

select * from openxml(@Idoc,'MGMsg/MsgHdr',2)
With( MsgId     varchar(10),
                          MsgMajVsn varchar(20),
                          MsgMinVsn varchar(20),
                          CrtnDm varchar(20),
                          LastProcessedDm varchar(20),
                          MsgOrgnatorAppl varchar(20),
                          MsgGenAddr varchar(20),
                          LastProcessedBy varchar(20)
                          )
EXEC SP_XML_REMOVEDOCUMENT @idoc

[解决办法]



DECLARE @idoc int
DECLARE @doc XML
set @doc ='<MGMsg >
 <MsgHdr>
  <MsgId>MG-BB02</MsgId> 
  <MsgMajVsn>1</MsgMajVsn> 
  <MsgMinVsn>0</MsgMinVsn> 
  <CrtnDm>16/10/12 14:04</CrtnDm> 
  <LastProcessedDm>16/10/12 14:04</LastProcessedDm> 
  <MsgOrgnatorAppl>SYS</MsgOrgnatorAppl> 
  <MsgGenAddr>PEK-HH</MsgGenAddr> 
  <LastProcessedBy>SYS</LastProcessedBy> 
  </MsgHdr>
</MGMsg>'
EXEC sp_xml_preparedocument @idoc OUTPUT,@doc
print @idoc

select * from openxml(@Idoc,'MGMsg/MsgHdr',2) 
With( MsgId     varchar(10),
                          MsgMajVsn varchar(20), 
 
                          MsgMinVsn varchar(20),
                          CrtnDm varchar(20),
                          LastProcessedDm varchar(20),
                          MsgOrgnatorAppl varchar(20),
                          MsgGenAddr varchar(20),
                          LastProcessedBy varchar(20)
                          )
EXEC SP_XML_REMOVEDOCUMENT @idoc

热点排行

SQL Server

Sqlserver XML OpenXML没得到结果集记录解决方法