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的数据插入到 表中请各位高手赐教!
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
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 行受影响)