April 28, 2010 at 6:58 pm
More errors in code ..
My orginal table was like this:
CREATE TABLE [dbo].[DIM_OHLC_Xml](
[Symbol] [varchar](50) NOT NULL,
[OHLC_XML] [xml] NOT NULL,
[CreatedOn] [datetime] NULL,
[LastUpdate] [datetime] NULL,
[Deleted] [bit] NULL CONSTRAINT [DF_DIM_OHLC_Xml_Deleted] DEFAULT ((0)),
[DeletedTime] [datetime] NULL,
CONSTRAINT [PK_DIM_OHLC_Xml] PRIMARY KEY CLUSTERED
(
[Symbol] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
This code gives this error...
Invalid column name 'OHLC_XML'.
The XMLDT method 'nodes' can only be invoked on columns of type xml.
select DATE,[OPEN],HIGH,LOW,[CLOSE],VOLUME,ADJCLOSE from (
SELECT
c.value('@date[1]','datetime') AS [DATE],
c.value('@open[1]','DECIMAL(9,2)') AS [OPEN],
c.value('@high[1]','DECIMAL(9,2)') AS [high],
c.value('@low[1]','DECIMAL(9,2)') AS [low],
c.value('@close[1]','DECIMAL(9,2)') AS [CLOSE],
c.value('@volime[1]','BIGINT') AS [volume],
c.value('@adjclose[1]','DECIMAL(9,2)') AS [adjclose]
FROM OHLC_XML.nodes('ohlc/record') T(c)
) as subqry
WHERE Symbol = 'c'
ORDER BY [date] ASC
I tried this : FROM dbo.DIM_OHLC_Xml.OHLC_XML.nodes('ohlc/record') T(c)
That didnt work...dbo.<tablename>.<fieldname>.nodes etc
April 29, 2010 at 3:38 am
Digs (4/28/2010)
I wish to hold a data dumpfor 1000 symbols with 1000's of records
In one table row.
What columns you want to store the data in?
Please review your provided information to be consistent.
What would really help us is:
Here is my input Data:
DECLARE @xml xml
Set @xml='...'
This is my expected result based on the input data:
CREATE TABLE ...
INSERT INTO SELECT
Here's what I've tried so far:
--Your code goes here
And here's where I'm getting stuck:
< description >
Right now it's getting even more confusing...
If you need assitance on how to provide the data as described above please see the first link in my signature.
April 29, 2010 at 1:59 pm
How could you be possible be confused.
My first post.
I wish to put data into a table I defined.
Then I wish to read data out of the table.
I wish to store many symbols in the table with XML data in an XML field.
This thread is about this.
I have posted two errors in code, so if some one could get over the confusion and help that would be great.
Lutz...this may be madness to you, but its a learning exercise for me as I test options. Please fix your code errors. thanks
April 29, 2010 at 3:44 pm
Digs (4/29/2010)
...Please fix your code errors. ...
Pardon me??
In your initial post you defined a table [dbo].[DIM_OHLC_Xml] without the columns you want to insert into based on the query you provided. After shredding the xml data into a relational structure you're asking how to query the xml data again. What would be the shredding process good for, then??
Matt and myself made you aware that it is better to shred the data and query the resulting table if you need a good performing solution. You never responded to that.
I kindly asked you to provide sample data in a ready to use format together with your expected results to make it easier for us to understand what you're trying to do, since it doesn't make sense (yet). Until now I'm still waiting what the table structure including sample data would look like at the end of the process you asked us to help you with.
I'll step back for now waiting for data provided as per the first link in my signature (including expected result set).
April 29, 2010 at 4:13 pm
Thanks for your helpm, bye!
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply