April 28, 2010 at 2:55 pm
HI,
This is my XML string built in vb.net (ie not a file import), and it can be upto 25000 records long. But still under the 2GB per XML field allowed.
<ohlc>
<record date="02/01/2010" open="125.25" high="126.25" low="124.25" close="125.20" volime="554651668" adjclose="125.20" />
<record date="02/02/2010" open="126.25" high="127.25" low="125.25" close="126.20" volime="548935488" adjclose="126.20" />
<record date="02/03/2010" open="125.25" high="126.25" low="124.25" close="125.20" volime="64651605" adjclose="125.20" />
</ohlc>
This the table I wish to store the XML into..
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 is my attemp to get the XML string ( that goes into @OHLCXML) from a VB net function into this table
CREATE PROCEDURE [dbo].[procPostOHLCNew](
@Symbol VARCHAR(50),
@OHLCXML VARCHAR(MAX)
)
AS
SET NOCOUNT ON
/*
Loads settings for charts/other by user
*/
DECLARE @IntErr INT
DECLARE @CurrDate DATETIME
DECLARE @intPointer INT
SET @CurrDate = getutcdate()
-- Run Posting to Table
SET LOCK_TIMEOUT 5000
BEGIN TRAN
BEGIN
SAVE TRANSACTION START
BEGIN
EXEC sp_xml_preparedocument @intPointer output, @OHLCXML
BEGIN
INSERT INTO dbo.DIM_OHLC_Xml
SELECT * FROM
OPENXML(@intPointer,'ohlc/record',2)
WITH([date] varchar(12),
[open] DECIMAL(9,2),
[high] DECIMAL(9,2),
[low] DECIMAL(9,2),
[close] DECIMAL(9,2),
[volume] BIGINT),
[adjclose] DECIMAL(9,2))
WHERE [date] NOT IN (SELECT [date] FROM record)
END
EXEC sp_xml_removedocument @intPointer
END
COMMIT TRANSACTION
SET @intErr = @@ERROR
IF @IntErr <> 0
BEGIN GOTO MYERROR END
END
SET NOCOUNT OFF
SELECT @IntErr
RETURN 0
MYERROR:
BEGIN
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION START
END
SET NOCOUNT OFF
SELECT @IntErr
RETURN -1
END
Questions
1) The table requires fields: symbol, CreatedOn to be populated with @Symbol and @CurrDate respectivily, BUT how does one do that in the OPENXML insert statement??
2) Hows does one, after insert, do a select statement on table to view OHLC data in a tabular format like
SELECT Open,High,Low,Close,Volume,adjClose FROM dbo.DIM_OHLC_Xml
WHERE Symbol = @Symbol
Please advise,thanks...:-)
April 28, 2010 at 3:57 pm
You might want to use XQuery instead of Openxml:
DECLARE @xml XML
SELECT @xml='<ohlc>
<record date="02/01/2010" open="125.25" high="126.25" low="124.25" close="125.20" volime="554651668" adjclose="125.20" />
<record date="02/02/2010" open="126.25" high="127.25" low="125.25" close="126.20" volime="548935488" adjclose="126.20" />
<record date="02/03/2010" open="125.25" high="126.25" low="124.25" close="125.20" volime="64651605" adjclose="125.20" />
</ohlc>'
DECLARE @Symbol CHAR(10), @CurrDate DATETIME
SET @symbol='abcd'
SET @CurrDate=GETDATE()
SELECT
@symbol AS Symbol,
@CurrDate AS Currdate,
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 @xml.nodes('ohlc/record') T(c)
April 28, 2010 at 4:08 pm
Sure whatever works..
So I just do
INSERT INTO dbo.DIM_OHLC_Xml (Symbol,CreatedOn,OHLC_XML)
SELECT
@symbol AS Symbol,
@CurrDate AS Currdate,
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 @xml.nodes('ohlc/record') T(c)
Questions
1) How do I do the 'WHERE [date] NOT IN (SELECT [date] FROM record) 'that I had in my first post??
2) I assume that [1] still allows 3 or 3000 records loaded ?
3) After I have done the XQuery insert into 'OHLC_XML' xml field, how do I do this type of query ..
.."SELECT Open,High,Low,Close,Volume,adjClose FROM dbo.DIM_OHLC_Xml
WHERE Symbol = @Symbol"...
4) I assume the @xml field you use can be an @XML varchar(max), as some of my xml uploaded will be 20,000 records long??
April 28, 2010 at 4:26 pm
Digs (4/28/2010)
Sure whatever works..So I just do
INSERT INTO dbo.DIM_OHLC_Xml
SELECT
@symbol AS Symbol,
@CurrDate AS Currdate,
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 @xml.nodes('ohlc/record') T(c)
Questions
1) How do I do the 'WHERE [date] NOT IN (SELECT [date] FROM record) 'that I had in my first post??
Try something like:
INSERT INTO dbo.DIM_OHLC_Xml
select * from (
SELECT
@symbol AS Symbol,
@CurrDate AS Currdate,
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 @xml.nodes('ohlc/record') T(c)) dataset
where dataset.[date] NOT IN (SELECT [date] FROM record)
2) I assume that [1] still allows 3 or 3000 records loaded ?
Yes - the @xml.nodes already split the nodes up into separate "rows". If you did that on @xml.VALUE, you'd end up only with the first value.
3) After I have done the XQuery insert into 'OHLC_XML' xml field, how do I do this type of query ..
.."SELECT Open,High,Low,Close,Volume,adjClose FROM dbo.DIM_OHLC_Xml
WHERE Symbol = @Symbol"...
4) I assume the @xml field you use can be an @XML varchar(max), as some of my xml uploaded will be 20,000 records long??
the XML datatype isn't a string, it's an entirely different data type, with distinctive capabilities you don't get on varchar(max). In order to tap into those capabilities (like format validation, and custom indexing capabilities) you need to actually use the XML data type (or - cast your varchar into XML on the fly and then index it, etc...)
The XML data type will accept up to 2GB.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 28, 2010 at 4:27 pm
Digs (4/28/2010)
Sure whatever works..So I just do
INSERT INTO dbo.DIM_OHLC_Xml
SELECT
@symbol AS Symbol,
@CurrDate AS Currdate,
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 @xml.nodes('ohlc/record') T(c)
Questions
1) How do I do the 'WHERE [date] NOT IN (SELECT [date] FROM record) 'that I had in my first post??
2) I assume that [1] still allows 3 or 3000 records loaded ?
3) After I have done the XQuery insert into 'OHLC_XML' xml field, how do I do this type of query ..
.."SELECT Open,High,Low,Close,Volume,adjClose FROM dbo.DIM_OHLC_Xml
WHERE Symbol = @Symbol"...
4) I assume the @xml field you use can be an @XML varchar(max), as some of my xml uploaded will be 20,000 records long??
Regarding your questions
q1: add the WHERE condition after the FROM statement as in any other standard SQL statement.
Side note: It might be more efficient to shred the data into a temp table, index it on the date column and use a left outer join rather than your NOT IN claus directly used against the XML structure. Test both versions and you'll see if it makes a difference...
q2: the [1] is relative to the attributes within each element. It doesn't matter how many recod elements you have, as long as there is only one date attribute per element.
q3: not sure about this one, since your insert statement doesnt match the table def you provided for dbo.DIM_OHLC_Xml... Assuming you'd like to select values from the already shredded table, use a standard T-SQL query (exactly as you mentioned it, just against a table that actually would have those columns...).
q4: Why wouldn't you use xml data type right away? 20k rows x approx. 150 char/row will be just a few MB. The limit for the xml data type is 2 GB.
April 28, 2010 at 4:30 pm
@matt-2: you beat me this time... :crying:
But at least our answers do not contradict... 😉
April 28, 2010 at 4:38 pm
Ok thanks you answered 1,2,4 for me,thanks.:-D
But you missed 3..
Once I have the @XML data in my XML field of my table.
I need to pull the data out so I can populate a IDatareader in the client with my vb.net. So I need a SELECT recordset that lookls like this..from the XML field
Date,Open,High,Low,Close,Volume,AdjClose
02/01/2010,125.25,126.25,124.25,125.20,
554651668,125.20
etc
So I wrote normal SQL I wrote ..
SELECT Open,High,Low,Close,Volume,adjClose FROM dbo.DIM_OHLC_Xml
WHERE [Symbol] = @Symbol
ORDER BY [Date] ASC
So How do i do an Xquery from the Field
is it like this...
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)
WHERE Symbol = @symbol)
ORDER BY [date] ASC
IMPORTANT
I will have upto 20000 records per symbol so I hope that Xquery is fast performance, and better than OPENXML ???
I need this XML to be a fast way as possible to populate a SELECT recordset as you see above for a IdataReader in vb.net.
Thanks for help...:-)
April 28, 2010 at 4:38 pm
lmu92 (4/28/2010)
@Matt: you beat me this time... :crying:But at least our answers do not contradict... 😉
Let's keep it that way! (not contradicting that is). I will let you get the next one!
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 28, 2010 at 4:45 pm
Is the symbol column supposed to be IN the xml snippet, or - outside of the XMl, in the outer table that holds the XML column? It changes how you do the query.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 28, 2010 at 4:50 pm
.."Is the symbol column supposed to be IN the xml snippet, or - outside of the XMl, in the outer table that holds the XML column? It changes how you do the query. "..
Symbol is oustide the result SELECT recordset that I need.
Sorry I dont understand your response, and could you please coment on my performance concern with 20,000 records...
April 28, 2010 at 4:52 pm
Digs (4/28/2010)
Ok thanks you answered 1,2,4 for me,thanks.:-DBut you missed 3..
...
No, I didn't. I told you that the information you provided so far is not consistent. You use columns in your query that don't exist in the table DDL you provided...
So far, I'm waiting for clarification.
Why would you shred the data into a relational table and perform a select against the xml data afterwards (as per your latest reply: So I need a SELECT recordset that lookls like this..from the XML field)? Doesn't make sense to me... Again, please clarify.
April 28, 2010 at 4:55 pm
Digs (4/28/2010)
.."Is the symbol column supposed to be IN the xml snippet, or - outside of the XMl, in the outer table that holds the XML column? It changes how you do the query. "..Symbol is oustide the result SELECT recordset that I need.
Sorry I dont understand your reponse, and could please coment on my performance concern with 20,000 records...
Never mind I saw it in the insert statement earlier. You're close:
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 = @symbol
ORDER BY [date] ASC
notice - I moved your Where clause outside of the subqyery (so you can use the aliases). Otherwise, just make sure you give your sub-query a name.
that said - Lutz' point is a good one. You take a penalty hit when you continuously shred and create XML (vs just hitting SQL tables directly): be careful not to over use that or you will slow your system down.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 28, 2010 at 4:57 pm
Digs (4/28/2010)
....Sorry I dont understand your response, and could you please coment on my performance concern with 20,000 records...
Your performance concern comes second. First we have to understand what you're actually trying to do. Right now to me it seems like you're storing one xml per @Symbol into dbo.DIM_OHLC_Xml. After that you try to shred the data into columns that don't exist as per your table def.
Finally you want to write a query against the xml data again.
Please describe the process you're actually trying to implement. I'm confused... :ermm::unsure:
April 28, 2010 at 6:36 pm
I wish to hold a data dump
for 1000 symbols with 1000's of records
In one table row.
April 28, 2010 at 6:39 pm
I get error with this code..Invalid object name 'record'.
Its part of the insert code that was provided. The where clause is important to ensure a record is not load with a duplicated date.
The 'record' should be refering to the already existing data in the table XML field named OHLC_XML
I guess this has to be something like OHLC_XML.nodes('ohlc/record')????
Thanks
DECLARE @OHLCXML XML
DECLARE @symbol VARCHAR(20)
DECLARE @CurrDate DATETIME
SET @CurrDate = getutcdate()
SET @symbol='C'
SET @OHLCXML = '<ohlc>
<record date="01/03/1977" open="38.88" high="39.13" low="38.88" close="39.13" volume="477600" adjclose="0.91" />
<record date="01/04/1977" open="39.13" high="39.25" low="39" close="39.25" volume="340800" adjclose="0.91" />
<record date="01/05/1977" open="39.25" high="39.25" low="38.88" close="39" volume="153600" adjclose="0.9" />
</ohlc>'
SELECT * from (
SELECT @symbol AS [Symbol],
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],
@CurrDate AS [CreatedOn]
FROM @OHLCXML.nodes('ohlc/record') T(c)) dataset
WHERE dataset.[date] NOT IN (SELECT [date] FROM record)
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply