|
|
|
Those of you that are familiar with sqlxml (particularly FOR XML EXPLICIT) may know it's a series of joins with coded column names representing the attributes for each element, and keyed to a parent to nest.
Column names look like this:
Tag Parent employee!1!employeeID customer!2!customerID customer!2!region
FOR XML EXPLICIT[^] for details.
Anyway, a long time ago a friend and I collaborated for work on a generalized SQLXML layer over any oledb source, not just SQL Server. It worked great.
And later I started on generalizing *that* to work on any sort of rowset, not just databases.
Such that you can (without oledb) craft a .csv for example using FOR XML EXPLICIT rules and get a reproduction of it in XML, or turn XML into a flat CSV.
CSV is just an example. Really anything implementing IEnumerable<IDataRow> or something.
The trouble is, it's a lot of work and I'm not sure if there would be any interest in such a thing these days.
It has been a long time since I've touched bizdev.
If you're interested, can you indicate it through a response emoji or letting me know in replies? If I get enough of it that it compels me then I'll write the article and the code.
To err is human. Fortune favors the monsters.
modified 30-Apr-23 2:05am.
|
|
|
|
|
I think more people are using JSON over XML now.
If you support both, you might draw more interest.
|
|
|
|
|
I was considering that, but I didn't want to promise it until I try it.
To err is human. Fortune favors the monsters.
|
|
|
|
|
Yes, except no, I already roll my own.
One utility I have is a fairly simple ETL tool which can read data from a number of disparate (tabular) sources (e.g. CSV, SQL Server, LDAP, SPLUNK, XML) and write it to a number of disparate (tabular) destinations (e.g. CSV, SQL Server, XML).
I have plans to add reading and writing JSON, but I have been putting it off.
Every type of source returns an IDataReader and every type of destination reads from an IDataReader.
There is some amount of flexibility for specifying the schema of the IDataReader, but not a lot.
The XML I produce is probably not what you would want though, and I don't have it configurable.
I would be interested in what you envision as a way of specifying the schema of the XML to produce.
|
|
|
|
|
PIEBALDconsult wrote: I would be interested in what you envision as a way of specifying the schema of the XML to produce.
That's where a lot of that work I was talking about comes in.
On that run where we did it, maybe back in 2004 or 2005, we used annotated XSD (XML Schema) and recognized mapping annotations that wherever we could, were already defined by microsoft.
So if you actually plug in a schema it can generate those column names I mentioned for you, or conversely, take tabular data and XMLize it.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Contact" sql:relation="Person.Contact" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="FName"
sql:field="FirstName"
type="xsd:string" />
<xsd:element name="LName"
sql:field="LastName"
type="xsd:string" />
</xsd:sequence>
<xsd:attribute name="ConID"
sql:field="ContactID"
type="xsd:integer" />
</xsd:complexType>
</xsd:element>
</xsd:schema>
And when we needed additional annotations we just added another namespace with more attributes defined in it, like microsoft did with the "msdata" namespace (that's just the prefix, i forget the URN)
or, alternatively, if you're going the explicit route you can make your csv field a nested element it would be like employee!1!employeeID!element instead of just employee!1!employeeID
One problem you'll find with JSON, is while XML makes sparse use of "arrays" in xml attributes, JSON basically uses arrays in its fields everywhere. When you go to map that to relational data, you'll have to come up with a scheme probably to pack subitems into a field using a join, and it will make your rowset significantly bigger breaking it out like that, but the alternative is denormalizing into a delimited list, if that makes sense. That might be tricky with JSON because you lose normalization of any nested objects.
Also remember arrays are ordered but fields are not in JSON.
Aside from that, less datatypes, but in some ways that makes it easier.
If you want to support JSON schema it's going to be quite difficult, but then so is XSD, because they're both lexically oriented rather than data oriented.
To err is human. Fortune favors the monsters.
|
|
|
|
|
I do have a utility which reads (non-tabular) Active Directory objects via LDAP and writes (non-tabular) JSON, so I already know how to write JSON. But I did that only because I was pushed into a corner; I would rather not have written the JSON-writing code at all, it was expedient.
Bear in mind that the utility I mentioned earlier uses an IDataReader as a "pipe" through which the data flows from source to destination. This really supports only tabular data and the names and types of the columns are known, so there is no requirement to have the script specify them. Having said that... I also have a source which wraps another source and has the ability to change the order, names, and types of the columns, and even remove columns. Any denormalization (I don't "pack subitems into a field") happens in the source so the destination is guaranteed to receive tabular data. Similarly, I don't worry about "any nested objects".
If some ETL process requires producing a JSON or XML file with nested objects or subitems, then it should not use this utility.
Because what you are describing applies only to the production of XML data, it would affect only my XML destination and I'll have to review that to see if what you are describing might help add some flexibility to it.
Probably my current schema would remain the default, but the script could specify a more complex schema if required.
|
|
|
|
|
honey the codewitch wrote: representing the attributes for each element
Myself I took my view of XML from a IBM doc that I read years ago.
Attributes should be used for non-critical data.
Elements should be used for all other data.
honey the codewitch wrote: on a generalized SQLXML layer over any oledb source
You know there are all sorts of ETL solutions these days right? Rolling it yourself might be interesting but perhaps not cost effective.
|
|
|
|
|
jschell wrote: Attributes should be used for non-critical data.
Elements should be used for all other data.
How people choose to orchestrate their schemas isn't really my concern, as you would structure your columns accordingly.
jschell wrote: You know there are all sorts of ETL solutions these days right? Rolling it yourself might be interesting but perhaps not cost effective
No, I wrote that in like 2004-2005 with some help.
What I'm talking about is something even more general: A largely dependency free data agnostic way to transform hierarchies to and from relational tabular data.
It may have been done. I wouldn't know, or I wouldn't even be asking if I knew it had been done.
The entity framework, "ETL solutions", et al, all came after I escaped that particular subfield with my life. 
To err is human. Fortune favors the monsters.
|
|
|
|
|
I use attributes for meta-data about the value -- e.g. Datatype, Isnull.
|
|
|
|
|
I would not consider that to be metadata for a database row value.
One of the posts above has an attribute that expands on the name from 'FName' to 'FirstName'. So that is a description and not normally significant nor critical.
You can of course represent 'null' with the following idiom just as easily.
With a value
<name>AColumn</name>
<value>xxx</value>
With empty value
<name>AColumn</name>
<value>xxx</value>
With null value
<name>AColumn</name>
If you want/need to make it explicit than the following works
<name>AColumn</name>
<Null/>
|
|
|
|
|
Really? I can't believe I've been mispronouncing it this whole time.
*hides*
To err is human. Fortune favors the monsters.
|
|
|
|
|
honey the codewitch wrote: hides
And with good reason.
Freedom is the freedom to say that two plus two make four. If that is granted, all else follows.
-- 6079 Smith W.
|
|
|
|
|
"A little time, a little trouble, your better day"
Badfinger
|
|
|
|
|
#Worldle #463 4/6 (100%)
🟩🟩🟩🟩⬜⬅️
🟩🟩🟩🟩🟨↗️
🟩🟩🟩🟩🟨⬅️
🟩🟩🟩🟩🟩🎉
https://worldle.teuteuf.fr
ran all around it
"A little time, a little trouble, your better day"
Badfinger
|
|
|
|
|
Wordle 680 3/6
⬛⬛🟩⬛⬛
⬛⬛🟩⬛🟨
🟩🟩🟩🟩🟩
|
|
|
|
|
Wordle 680 5/6
⬛🟩🟩⬛⬛
🟩🟩🟩⬛⬛
🟩🟩🟩⬛⬛
🟩🟩🟩⬛🟩
🟩🟩🟩🟩🟩
How did you get that in 3???
|
|
|
|
|
Starter word, probably.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
After 1 & 2, there was only two options that came to my mind for 3rd as something that fits in.
|
|
|
|
|
If you reside in Bengaluru, India, the place where the MG Road Metro Station is, was earlier a cinema theatre by that name. Have seen many movies there.
Hope this isn't a spoiler.
|
|
|
|
|
Wordle 680 5/6
🟨⬜⬜⬜⬜
⬜🟨⬜⬜⬜
⬜⬜🟩🟨⬜
🟩🟩🟩⬜⬜
🟩🟩🟩🟩🟩
|
|
|
|
|
Wordle 680 3/6*
🟨⬜⬜⬜⬜
🟩🟨⬜⬜⬜
🟩🟩🟩🟩🟩
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
Wordle 680 4/6
⬜⬜🟩⬜⬜
⬜⬜🟩⬜⬜
⬜🟩🟩⬜⬜
🟩🟩🟩🟩🟩
In a closed society where everybody's guilty, the only crime is getting caught. In a world of thieves, the only final sin is stupidity. - Hunter S Thompson - RIP
|
|
|
|
|
Wordle 680 4/6
⬜⬜⬜⬜⬜
⬜⬜⬜⬜⬜
⬜🟩🟩⬜⬜
🟩🟩🟩🟩🟩
Just had about any letter in the alphabet 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
(var to the rescue!) 
