[blindman]

Just found out my client requires our application to export this data for import into their data warehouse:

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

[georgev]

They REQUIRE it in that format?
Oh blindman, I feel for you

[blindman]

I suppose it could be worse. At least I don't have to import it!

[Pat Phelan]

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

[r937]

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

[blindman]

The whole idea of putting EAV into XML format is redundant. Like smearing a turd with feces.

[certus]

I don't want to see the data warehouse.

[Pat Phelan]

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

[r937]

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

[blindman]

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

[Pat Phelan]

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

[blindman]

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.

[sco08y]

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.

[sco08y]

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.

[blindman]

Again, encapsulating EAV in XML is needlessly redundant. XML already is an "EAV" type of schema.