SQL 查询，批量录入。

[振乾]

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的数据插入到 表中

请各位高手赐教!

[q806294478]

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 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>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlDoc
insert into MeteoReport(code4,utctime,reportType)
SELECT    *
FROM       OPENXML (@idoc, '/MeteoReport/item',1)
WITH (
    code4 VARCHAR(50),
    utctime DATETIME,
    [reportType] [INT]
                  )
EXEC sp_xml_removedocument @idoc
select * from MeteoReport

结果为：
ID	code4	utctime	reportType
1	ZSSD	2012-02-09 00:00:00.000	1
2	PANC	2012-02-09 00:00:00.000	2
3	ZSGS	2012-02-09 00:00:00.000	0

(3 行受影响)

(3 行受影响)

[jmx123456789]

[Quote=引用 2 楼 q806294478 的回复:]
SQL code

[/Quote]

mark

[老猫五号]

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