Home   |   Register   |   Rules   |   Calendar   |   Get Daily   |     |  
 


If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > General > Database Concepts & Design > Dear God, no.....

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 09-27-07, 09:27
blindman blindman is offline
World Class Flame Warrior
  
Join Date: Jun 2003
Location: Ohio
Posts: 11,726
Dear God, no.....
Just found out my client requires our application to export this data for import into their data warehouse:
Code:
<xxBALCALC>
	<BALCALC>
		<MASTER_KEY>11913</MASTER_KEY> 
		<CALC_DATE>39294</CALC_DATE> 
		<DATAKEY>MASTER_UID</DATAKEY> 
		<DATAVALUE>1114386.401</DATAVALUE> 
	</BALCALC>
	<BALCALC>
		<MASTER_KEY>11913</MASTER_KEY> 
		<CALC_DATE>39294</CALC_DATE> 
		<DATAKEY>SSN</DATAKEY> 
		<DATAVALUE /> 
	</BALCALC>
	<BALCALC>
		<MASTER_KEY>11913</MASTER_KEY> 
		<CALC_DATE>39294</CALC_DATE> 
		<DATAKEY>CALC_METHOD</DATAKEY> 
		<DATAVALUE>CALENDAR YEAR (01/01/2007 THROUGH 12/31/2007)</DATAVALUE> 
	</BALCALC>
	<BALCALC>
		<MASTER_KEY>11913</MASTER_KEY> 
		<CALC_DATE>39294</CALC_DATE> 
		<DATAKEY>CALC_WARNINGS</DATAKEY> 
		<DATAVALUE /> 
	</BALCALC>
	<BALCALC>
		<MASTER_KEY>11913</MASTER_KEY> 
		<CALC_DATE>39294</CALC_DATE> 
		<DATAKEY>CALC_ERRORS</DATAKEY> 
		<DATAVALUE /> 
	</BALCALC>
	.
	.
	. etc...
Look at it. That's right. Its EAV in XML. I'm going to go cry in a corner for a while....
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
Reply With Quote
  #2 (permalink)  
Old 09-27-07, 10:18
gvee gvee is offline
www.gvee.co.uk
  
Join Date: Jan 2007
Location: UK
Posts: 10,156
They REQUIRE it in that format?
Oh blindman, I feel for you
__________________
George
Twitter | Blog
Reply With Quote
  #3 (permalink)  
Old 09-27-07, 11:30
blindman blindman is offline
World Class Flame Warrior
  
Join Date: Jun 2003
Location: Ohio
Posts: 11,726
I suppose it could be worse. At least I don't have to import it!
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
Reply With Quote
  #4 (permalink)  
Old 09-27-07, 11:52
Pat Phelan Pat Phelan is offline
Resident Curmudgeon
  
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,605
Maybe I'm doing drugs (well, that's a gimme but I'm referring to more interesting drugs), but that looks like a pretty straightforward XML schema to me. The xxBALCALC schema element corresponds to a SQL table, the BALCALC element corresponds to a row, and the individual sub-elements inside of BALCALC are all present in every BALCALC element. Unless I'm missing something, that is about as regular as XML gets, and a near perfect translation of the rectangular row/column view of data used by SQL.

What am I missing?

-PatP
Reply With Quote
  #5 (permalink)  
Old 09-27-07, 12:40
r937 r937 is offline
SQL Consultant
  
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
Quote:
Originally Posted by Pat Phelan
What am I missing?
the fact that it's an EAV scheme

also, dates as "excel numbers" e.g. 39294
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #6 (permalink)  
Old 09-27-07, 13:01
blindman blindman is offline
World Class Flame Warrior
  
Join Date: Jun 2003
Location: Ohio
Posts: 11,726
The whole idea of putting EAV into XML format is redundant. Like smearing a turd with feces.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
Reply With Quote
  #7 (permalink)  
Old 09-27-07, 13:39
certus certus is offline
Registered User
  
Join Date: Dec 2003
Location: Canada
Posts: 710
I don't want to see the data warehouse.
__________________
visit: relationary
Reply With Quote
  #8 (permalink)  
Old 09-27-07, 13:43
Pat Phelan Pat Phelan is offline
Resident Curmudgeon
  
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,605
XML definitely is an EAV scheme, that is part of its design (in other words a feature, not a bug). That particular XML schema is perfectly regular, no sub-elements change from one element to the next as far as I can see.

Is what you are trying to tell me that the DATAKEY values are actually EAV attribute names stored inside of the XML rather than being simply data?

-PatP
Reply With Quote
  #9 (permalink)  
Old 09-27-07, 13:58
r937 r937 is offline
SQL Consultant
  
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
yes, pat, that's what we're trying to tell you

the DATAKEY looks like an attribute name and the DATAVALUE looks like an attribute value

as you said, it is "a near perfect translation of the rectangular row/column view," but of an EAV scheme, not a normal relational data table
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #10 (permalink)  
Old 09-27-07, 14:15
blindman blindman is offline
World Class Flame Warrior
  
Join Date: Jun 2003
Location: Ohio
Posts: 11,726
...and (naturally) my data is not in an EAV schema. So I'm thinking I'll need a hairy-ass UNION query with 20 or more SELECTs to join in each element they require.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
Reply With Quote
  #11 (permalink)  
Old 09-27-07, 14:55
Pat Phelan Pat Phelan is offline
Resident Curmudgeon
  
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,605
Just curioius, but when I create XML I usually use an application or script to do it rather than trying to build the XML directly from a database query. Even if you choose to build the XML using a cursor in Transact-SQL, this is just sinfully ugly, not hairy to do...

Just so I understand, you're wailing about the ugly factor, not complaining because of difficulty, correct?

-PatP
Reply With Quote
  #12 (permalink)  
Old 09-27-07, 15:25
blindman blindman is offline
World Class Flame Warrior
  
Join Date: Jun 2003
Location: Ohio
Posts: 11,726
Fortunately this development is in 2005, so I can just use "Select .... for xml auto". Its the UNION query that will be tedious and ugly.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
Reply With Quote
  #13 (permalink)  
Old 09-28-07, 23:13
sco08y sco08y is offline
Registered User
  
Join Date: Oct 2002
Location: Baghdad, Iraq
Posts: 697
Quote:
Originally Posted by r937
the fact that it's an EAV scheme

also, dates as "excel numbers" e.g. 39294
Now, hold on a second here: "excel numbers" are Julian dates with an epoch of 1 Jan 1900. Julian dates are a perfectly logical way of representing dates.
Reply With Quote
  #14 (permalink)  
Old 09-28-07, 23:22
sco08y sco08y is offline
Registered User
  
Join Date: Oct 2002
Location: Baghdad, Iraq
Posts: 697
Quote:
Originally Posted by blindman
Just found out my client requires our application to export this data for import into their data warehouse: ... Look at it. That's right. Its EAV in XML. I'm going to go cry in a corner for a while....
I'm on record here as complaining about EAV... but only as a storage mechanism and a permanent structure. (Because it makes queries a PITA and because you can't impose integrity constraints on it.)

But for transmitting data, it makes sense. You could pretty much do a one to one translation of BALCALC elements to SQL statements for importing.
Reply With Quote
  #15 (permalink)  
Old 09-29-07, 16:59
blindman blindman is offline
World Class Flame Warrior
  
Join Date: Jun 2003
Location: Ohio
Posts: 11,726
Again, encapsulating EAV in XML is needlessly redundant. XML already is an "EAV" type of schema.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
Reply With Quote
Reply

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


 
 
 
Home  |   Register  |   Get New  |   FAQ's  |   Sitemap

Powered by vBulletin
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.