存取过程的调用数据为:
exec usp_mz_sf_save @ID=-1,@GHLSH=30,@DWDM=N'320482105990002',@DATA=N'<NewDataSet>
<Master>
<GHLSH>30</GHLSH>
<DJH>2010003194</DJH>
<MZH>201007070002</MZH>
<XM>张三</XM>
<XB>男</XB>
<CSRQ>2010-7-7 </CSRQ>
<SFZH>1212121212 </SFZH>
<CZRYBM>629</CZRYBM>
<CZRYMC>789</CZRYMC>
<KSDM />
<KSMC />
<YSDM>629</YSDM>
<YSMC>789</YSMC>
<ZDMC />
<ZDMC1 />
<JSRYBM>629</JSRYBM>
<JSRYMC>789</JSRYMC>
<ZS />
<XBS />
<JC />
</Master>
<Detail>
<wssdm>320482105990002</wssdm>
<itemcode>110100001-a</itemcode>
<itemname>急诊挂号费</itemname>
<itemgg xml:space="preserve"> </itemgg>
<itemunit />
<itemprice>1.2000</itemprice>
<itemcount>1</itemcount>
<je>1.2000</je>
<ybdzm>110100001-a</ybdzm>
<ypdwzhxs>0</ypdwzhxs>
<sfxmflmc>其他</sfxmflmc>
<DailyTimes />
<DosageEveryTime />
<CommonUseQuantity />
<yznr />
<yldw />
<ypzkl>0</ypzkl>
</Detail>
</NewDataSet>'
解析代码为下:
declare @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @DATA
SELECT * into #MASTER
FROM OPENXML (@idoc, '/NewDataSet/Master',2)
WITH
(
ID bigint,
WSSDM varchar(20),
BRID varchar(50),
DJH varchar(50),
MZH varchar(20),
XM varchar(32),
XB char(2),
CSRQ char(10),
SFZH char(18),
JZDZ varchar(64),
LXDH varchar(18),
KSDM varchar(16) ,
KSMC varchar(32) ,
YSDM varchar(20) ,
YSMC varchar(32) ,
CZSJ varchar(20) ,
CZRYBM varchar(20) ,
CZRYMC varchar(32) ,
JSSJ varchar(20) ,
JSRYBM varchar(20) ,
JSRYMC varchar(32) ,
KLX char(2),
KBH varchar(50) ,
ZXLSH varchar(50) ,
BCLSH varchar(50) ,
HCLSH varchar(50) ,
YLZH varchar(50) ,
XQBM varchar(50) ,
JTBM varchar(50) ,
BCLB varchar(20) ,
JZLB varchar(20) ,
GRBJH varchar(50) ,
FZBZ smallint ,
BRZT smallint ,
JSZT smallint ,
ZJE decimal(14, 4),
SRJE decimal(14, 4),
ZDDM varchar(30),
ZDMC varchar(60),
ZDDM1 varchar(30),
ZDMC1 varchar(60),
ZS varchar(100),
XBS varchar(100),
JC varchar(100)
)
EXEC sp_xml_removedocument @idoc
if @@ERROR <> 0
begin
select 'F' as rst,'解析主表失败!' as msg
goto ErrorHandle
end
EXEC sp_xml_preparedocument @idoc OUTPUT, @DATA
SELECT * into #DETAIL
FROM OPENXML (@idoc, '/NewDataSet/Detail',2)
WITH
(
id numeric(18, 0),
zb_id numeric(18, 0),
wssdm varchar(20),
keysf varchar(20),
mzh varchar(20),
djh varchar(50),
cfh varchar(20),
itemclass varchar(4),
itemcode varchar(20),
itemname varchar(64),
itemgg varchar(60),
itemunit varchar(6),
itemprice decimal(10, 4),
itemcount decimal(10, 2),
tjbm varchar(10),
mzxmbm varchar(10),
zyts int,
je decimal(14, 4),
zfje decimal(10, 2),
yhje decimal(10, 2),
jyqq tinyint,
jylsh varchar(50),
jyqx char(1),
qxlsh varchar(50),
ylsh varchar(50),
ybje decimal(10, 2),
ybdzm varchar(20),
ypdwzhxs int,
sfxmflmc varchar(30),
fyzt smallint,
zxks varchar(16),
zxksmc varchar(32),
dailytimes varchar(4),
dosageeverytime varchar(4),
commonusequantity varchar(20),
yznr varchar(100),
yldw varchar(8),
ypfs decimal(10, 2),
ypzkl decimal(4, 2),
fzxh decimal(10, 0),
pcdm varchar(20)
)