SQL 读取XML到Datatable

 

 

DECLARE @hdoc INT
--XML 数据格式
---------------------------------------------------------
DECLARE @doc XML,@SHNO VARCHAR(60)
SET @doc =  '
<SALE>
    <SHNO>SH000000002</SHNO>
    <PRO>
        <BARCODE>
            <BOX_BARCODE>8800001</BOX_BARCODE>
            <PRD_NO>5000001</PRD_NO>
        </BARCODE>
        <BARCODE>
            <BOX_BARCODE>8800002</BOX_BARCODE>
            <PRD_NO>5000001</PRD_NO>
        </BARCODE>
    </PRO>
</SALE>';
EXEC sp_xml_preparedocument @hdoc OUTPUT , @doc ;
SELECT BOX_BARCODE,PRD_NO INTO #Item
FROM openxml (@hdoc, '/SALE/PRO/BARCODE',2 )
WITH ( BOX_BARCODE VARCHAR(60 ),PRD_NO VARCHAR(60) );
 
SELECT TOP 1 @SHNO = SHNO
FROM openxml (@hdoc, '/SALE',2 )
WITH (  SHNO VARCHAR(60) ) ;