SQL 查询，批量录入。解决方案

SQL 查询，批量录入。

SQL code

IF(OBJECT_ID('MeteoReport') IS NOT null)  DROP TABLE MeteoReport    CREATE TABLE MeteoReport     (    ID INT PRIMARY KEY IDENTITY(1,1),    code4 VARCHAR(50),    utctime DATETIME,    reprottype INT    )declare @xmlDoc xml;  set @xmlDoc='<MeteoReport>     <item code4="ZSSD" utctime="2012-2-9" reportType=1 />    <item code4="PANC" utctime="2012-2-9" reportType=2 />    <item code4="ZSGS" utctime="2012-2-9" reportType=0 />    </MeteoReport>' --1.如何对XML进行 查询显示？ --2.如何将xml的数据插入到 表中

请各位高手赐教!

[解决办法]
DECLARE @x xml
SET @x=' 
<Root> 
<row id="1"><name>Larry</name><oflw>some text</oflw></row> 
<row id="2"><name>Joe</name></row> 
<row id="3" /> 
</Root> 
' 
SELECT T.c.value('@id','int') as id, 
T.c.query('name') as NAME 
FROM @x.nodes('/Root/row') T(c) 
go
[解决办法]

SQL code

IF(OBJECT_ID('MeteoReport') IS NOT null)  DROP TABLE MeteoReport    CREATE TABLE MeteoReport     (    ID INT PRIMARY KEY IDENTITY(1,1),    code4 VARCHAR(50),    utctime DATETIME,    reportType INT    )declare @idoc intdeclare @xmlDoc xml;  set @xmlDoc='<MeteoReport>     <item code4="ZSSD" utctime="2012-2-9" reportType="1" />    <item code4="PANC" utctime="2012-2-9" reportType="2" />    <item code4="ZSGS" utctime="2012-2-9" reportType="0" />    </MeteoReport>'EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlDocinsert into MeteoReport(code4,utctime,reportType)SELECT    *FROM       OPENXML (@idoc, '/MeteoReport/item',1)WITH (    code4 VARCHAR(50),    utctime DATETIME,    [reportType] [INT]                  )EXEC sp_xml_removedocument @idocselect * from MeteoReport结果为：ID    code4    utctime    reportType1    ZSSD    2012-02-09 00:00:00.000    12    PANC    2012-02-09 00:00:00.000    23    ZSGS    2012-02-09 00:00:00.000    0(3 行受影响)(3 行受影响)
[解决办法]
探讨

SQL code

 

 

查看更多 下一篇