Selecting from an XML datatype into a table

Mel Padden

Hi all. I'm trying to use an XML datatype to pass around tabular data between functions, and I'm a bit unfamiliar with both this and XQuery syntax, so this is troublesome for me. I have a piece of code like so:

DECLARE @time_series XML;
SET @time_series =
'<timeSeries>
<ts><date>2010-06-30</date><value>1.235648</value></ts>
<ts><date>2010-07-31</date><value>564654.235648</value></ts>
<ts><date>2010-08-31</date><value>5465465.235648</value></ts>
<ts><date>2010-09-30</date><value>952031.235648</value></ts>
</timeSeries>'
;

DECLARE @tbl_time_series TABLE(
ts_date DATETIME
, ts_value FLOAT
);

INSERT INTO @tbl_time_series
SELECT
TS.item.nodes('/date').value('.', 'datetime') AS tsdate
, TS.item.nodes('/value').value('.', 'float') AS tsvalue
FROM
@time_series.nodes('timeSeries/ts') AS TS(item)
;

"item", by my reasoning, should allow me to select the date node underneath it in the select, but it appears not. I get this message:

Msg 227, Level 15, State 1, Line 19
"nodes" is not a valid function, property, or field.

All I want is to end up with a table like this;

ts\_date		ts\_value
----------	--------------
2010-06-30	1.235648
2010-07-31	564654.235648
2010-08-31	5465465.235648
2010-09-30	952031.235648

when I try this:

INSERT INTO @tbl_time_series
SELECT
TS.item.query('/date').value('.', 'datetime') AS tsdate
, TS.item.query('/value').value('.', 'float') AS tsvalue
FROM
@time_series.nodes('timeSeries/ts') AS TS(item)
;

See the query in the select statement instead of the node? this runs alright, but I get a result set like this:

1900-01-01 00:00:00.000 0
1900-01-01 00:00:00.000 0
1900-01-01 00:00:00.000 0
1900-01-01 00:00:00.000 0

I think I'm nearly there but my XQuery noobiness is failing me... :^) :^) Any suggestions?

Smokie, this is not 'Nam. This is bowling. There are rules. www.geticeberg.com

Mel Padden

UPDATE Solved. Here's the final statement.

DECLARE @time_series XML;
SET @time_series =
'<timeSeries>
<ts><date>2010-06-30</date><value>1.235648</value></ts>
<ts><date>2010-07-31</date><value>564654.235648</value></ts>
<ts><date>2010-08-31</date><value>5465465.235648</value></ts>
<ts><date>2010-09-30</date><value>952031.235648</value></ts>
</timeSeries>'
;

DECLARE @tbl_time_series TABLE(
ts_date DATETIME
, ts_value FLOAT
);

INSERT INTO @tbl_time_series
SELECT
TS.item.query('date').value('.', 'datetime') AS tsdate
, TS.item.query('value').value('.', 'float') AS tsvalue
FROM
@time_series.nodes('timeSeries/ts') AS TS(item)
;

SELECT \* FROM @tbl\_time\_series;

Just as I suspected it was my lack of XQuery-fu... d'oh! Hope someone else finds this useful anyway.

Smokie, this is not 'Nam. This is bowling. There are rules. www.geticeberg.com