|
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
| 
转播
