mssql 解析XML格式到表

 

DECLARE @XML NVARCHAR(MAX);
SET @XML = '<NewDataSet>
    <Table>
        <material_id>1</material_id>
        <material_no>1</material_no>
        <material_name>PP</material_name>
        <isvalid>Y</isvalid>
    </Table>
    <Table>
        <material_id>2</material_id>
        <material_no>2</material_no>
        <material_name>PA6</material_name>
        <isvalid>N</isvalid>
        <upd_date>2021-01-26T22:22:00+08:00</upd_date>
        <upd_user>蔡显洪</upd_user>
    </Table>
    <Table>
        <material_id>3</material_id>
        <material_no>3</material_no>
        <material_name>ADC12</material_name>
        <isvalid>Y</isvalid>
    </Table>
    <Table>
        <material_id>4</material_id>
        <material_no>6</material_no>
        <material_name>橡胶</material_name>
        <isvalid>Y</isvalid>
    </Table>
    <Table>
        <material_id>5</material_id>
        <material_no>7</material_no>
        <material_name>铜</material_name>
        <isvalid>Y</isvalid>
    </Table>
    <Table>
        <material_id>6</material_id>
        <material_no>8</material_no>
        <material_name>铅</material_name>
        <isvalid>Y</isvalid>
    </Table>
    <Table>
        <material_id>7</material_id>
        <material_no>9</material_no>
        <material_name>铁</material_name>
        <isvalid>Y</isvalid>
    </Table>
    <Table>
        <material_id>8</material_id>
        <material_no>10</material_no>
        <material_name>ST12</material_name>
        <isvalid>Y</isvalid>
    </Table>
    <Table>
        <material_id>9</material_id>
        <material_no>11</material_no>
        <material_name>ST14</material_name>
        <isvalid>Y</isvalid>
    </Table>
    <Table>
        <material_id>10</material_id>
        <material_no>12</material_no>
        <material_name>热板</material_name>
        <isvalid>Y</isvalid>
    </Table>
    <Table>
        <material_id>11</material_id>
        <material_no>13</material_no>
        <material_name>40Cr</material_name>
        <isvalid>Y</isvalid>
    </Table>
    <Table>
        <material_id>12</material_id>
        <material_no>14</material_no>
        <material_name>特钢</material_name>
        <isvalid>Y</isvalid>
    </Table>
    <Table>
        <material_id>13</material_id>
        <material_no>15</material_no>
        <material_name>钢（机架）</material_name>
        <isvalid>Y</isvalid>
    </Table>
    <Table>
        <material_id>14</material_id>
        <material_no>16</material_no>
        <material_name>冷轧硅钢</material_name>
        <isvalid>Y</isvalid>
    </Table>
    <Table>
        <material_id>15</material_id>
        <material_no>17</material_no>
        <material_name>铝线</material_name>
        <isvalid>Y</isvalid>
    </Table>
    <Table>
        <material_id>16</material_id>
        <material_no>18</material_no>
        <material_name>AL00</material_name>
        <isvalid>Y</isvalid>
    </Table>
    <Table>
        <material_id>17</material_id>
        <material_no>19</material_no>
        <material_name>生铁</material_name>
        <material_cate />
        <isvalid>Y</isvalid>
    </Table>
</NewDataSet>';

DECLARE @handle INT;  
DECLARE @PrepareXmlStatus INT;  

EXEC @PrepareXmlStatus= sp_xml_preparedocument @handle OUTPUT, @XML;  

SELECT  *
FROM    OPENXML(@handle, '/NewDataSet/Table', 2)  
    WITH (
    material_id int,
    material_no NVARCHAR(30),
    material_name NVARCHAR(20),
    material_cate NVARCHAR(20),
    isvalid VARCHAR(1)
    );  


EXEC sp_xml_removedocument @handle; 

------ 存储过程处理

DECLARE @handle INT; 
DECLARE @PrepareXmlStatus INT;
DECLARE @xml NVARCHAR(MAX); 
SET @xml = REPLACE(@xmldata, '?', '');   -- 由于从C#传过来的xml字符串有问题
EXEC @PrepareXmlStatus= sp_xml_preparedocument @handle OUTPUT, @xml;  
IF OBJECT_ID('tempdb..#bomtemp') IS NOT NULL
    DROP TABLE #bomtemp; 

SELECT * INTO #bomtemp FROM (  
SELECT  *  
FROM    OPENXML(@handle, '/NewDataSet/Table', 2)    
    WITH (  
     org_id int ,  
	 prd_no varchar(100) ,  
	 cust_no varchar(20) ,  
	 sal_groupno varchar(10),  
	 cust_nm VARCHAR(50),  
     BOM_WZX NVARCHAR(20)  
    )  
) a   
  
    --SELECT TOP 100 * FROM #bomtemp