22,209
社区成员
发帖
与我相关
我的任务
分享
declare @FromPalletXML xml
set @FromPalletXML='
<NewDataSet>
<Table>
<whpi_iTagID>1</whpi_iTagID>
<whpi_cTagNo>BB1983</whpi_cTagNo>
<whpi_cOperationStatusCD>P10</whpi_cOperationStatusCD>
<whpi_cOperationStatusDesc>卡板已經做過入板操作</whpi_cOperationStatusDesc>
<whpi_lGoodsFICTake>1</whpi_lGoodsFICTake>
<whpi_cFICstatusDesc>已抽貨</whpi_cFICstatusDesc>
<whpi_lGoodsFICResult>1</whpi_lGoodsFICResult>
<whpi_cFICResultDesc>合格</whpi_cFICResultDesc>
<Whpi_lAutoLoading>0</Whpi_lAutoLoading>
<Whpi_cGroupNo />
<Whpi_fPalletLength>120</Whpi_fPalletLength>
<Whpi_fPalletWeight>120</Whpi_fPalletWeight>
<Whpi_fPalletHeight>120</Whpi_fPalletHeight>
</Table>
</NewDataSet>'
declare @iDoc1 int
exec sp_xml_preparedocument @iDoc1 output,@FromPalletXML
select * --into #FromPallet
from
openxml(@iDoc1,'/NewDataSet/Table',2)
with
(
whpi_iTagID int ,
whpi_cTagNo varchar(24),
whpi_cOperationStatusCD varchar(3),
whpi_cOperationStatusDesc nvarchar(20),
whpi_lGoodsFICTake bit,
whpi_cFICstatusDesc nvarchar(10),
whpi_lGoodsFICResult bit,
whpi_cFICResultDesc nvarchar(10),
Whpi_lAutoLoading bit,
Whpi_cGroupNo varchar(20),
Whpi_fPalletLength decimal(18),
Whpi_fPalletWeight decimal(18),
Whpi_fPalletHeight decimal(18)
) As a
EXEC sp_xml_removedocument @iDoc1
whpi_iTagID whpi_cTagNo whpi_cOperationStatusCD whpi_cOperationStatusDesc whpi_lGoodsFICTake whpi_cFICstatusDesc whpi_lGoodsFICResult whpi_cFICResultDesc Whpi_lAutoLoading Whpi_cGroupNo Whpi_fPalletLength Whpi_fPalletWeight Whpi_fPalletHeight
----------- ------------------------ ----------------------- ------------------------- ------------------ ------------------- -------------------- ------------------- ----------------- -------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1 BB1983 P10 ?????????? 1 ??? 1 ?? 0 120 120 120
(1 row(s) affected)
最简单的加个N:
@FromPalletXML='
<NewDataSet>
<Table>
....'
数据库字符集问题
declare @PalletXML nvarchar(max)
--卡板資料XML
set @PalletXML='
<NewDataSet>
<Table>
<whpi_iTagID>1</whpi_iTagID>
<whpi_cTagNo>BB1983</whpi_cTagNo>
<whpi_cOperationStatusCD>P10</whpi_cOperationStatusCD>
<whpi_cOperationStatusDesc>卡板已經做過入板操作</whpi_cOperationStatusDesc>
<whpi_cGoodsFICTake>1</whpi_cGoodsFICTake>
<whpi_cFICstatusDesc>合格</whpi_cFICstatusDesc>
<Whpi_lAutoLoading>0</Whpi_lAutoLoading>
<Whpi_cGroupNo />
<Whpi_fPalletLength>120</Whpi_fPalletLength>
<Whpi_fPalletWeight>120</Whpi_fPalletWeight>
<Whpi_fPalletHeight>120</Whpi_fPalletHeight>
</Table>
</NewDataSet>'
select @PalletXML
<NewDataSet>
<Table>
<whpi_iTagID>1</whpi_iTagID>
<whpi_cTagNo>BB1983</whpi_cTagNo>
<whpi_cOperationStatusCD>P10</whpi_cOperationStatusCD>
<whpi_cOperationStatusDesc>??????????</whpi_cOperationStatusDesc>
<whpi_cGoodsFICTake>1
(1 row(s) affected)