Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592

    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"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    They REQUIRE it in that format?
    Oh blindman, I feel for you
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    I don't want to see the data warehouse.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    ...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"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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"
    www.LobsterShot.blogspot.com

  13. #13
    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.

  14. #14
    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.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •