CREATE TABLE TPointValue(
PointID nvarchar(30) ,
PointValue xml
) INSERT INTO TPointValue(PointID,PointValue)
VALUES ('10','<?xml version="1.0" encoding="UTF-8"?>
<RMC_Data>
<Value>150</Value>
<Date>20090724</Date>
</RMC_Data>
<RMC_Data>
<Value>100</Value>
<Date>20090725</Date>
</RMC_Data>
')
INSERT INTO TPointValue(PointID,PointValue)
VALUES ('11','<?xml version="1.0" encoding="UTF-8"?>
<RMC_Data>
<Value>15</Value>
<Date>20090724</Date>
</RMC_Data>
<RMC_Data>
<Value>10</Value>
<Date>20090725</Date>
</RMC_Data>
')
前面是建表和增加两笔数据,
想得到的結果是
PointID Value Date
------------------------------------
10 150 20090724
10 100 20090725
11 15 20090724
11 10 20090725该怎样查啊?
PointID nvarchar(30) ,
PointValue xml
) INSERT INTO TPointValue(PointID,PointValue)
VALUES ('10','<?xml version="1.0" encoding="UTF-8"?>
<RMC_Data>
<Value>150</Value>
<Date>20090724</Date>
</RMC_Data>
<RMC_Data>
<Value>100</Value>
<Date>20090725</Date>
</RMC_Data>
')
INSERT INTO TPointValue(PointID,PointValue)
VALUES ('11','<?xml version="1.0" encoding="UTF-8"?>
<RMC_Data>
<Value>15</Value>
<Date>20090724</Date>
</RMC_Data>
<RMC_Data>
<Value>10</Value>
<Date>20090725</Date>
</RMC_Data>
')SELECT
A.PointID,
B.x.value('Value[1]','int') AS [Value],
B.x.value('Date[1]','datetime') AS [Date]
FROM TPointValue AS A
OUTER APPLY A.PointValue.nodes('//RMC_Data') AS B(x)DROP TABLE TPointValue/*
PointID Value Date
------------------------------ ----------- -----------------------
10 150 2009-07-24 00:00:00.000
10 100 2009-07-25 00:00:00.000
11 15 2009-07-24 00:00:00.000
11 10 2009-07-25 00:00:00.000(4 行受影响)*/