has anyone seen a custom meta data driven json generator?
-
Hi, we might be interested in abstracting the generation of a union all amongst queries that run over differently formatted purchase order tables.
What do i mean? We buy things whose attribute collections/columns differ in 3 ways and therefore come across in 3 differently formatted purchase order tables. But then translating the extracted info into json allows for a union all of the same number and types of columns. We are picturing a biz rule meta data table that would drive this from a call to one general function we would write or copy. The meta data biz table would know what columns need to be extracted from each po table and in what order. the calls to the function would be made by some high level query that passes perhaps table name and core keys to the function in a set based query.
Here's more. PO table A might have in addition to key info, color and weight columns because product lines that go in that table are characterized by only those 2 attributes. Table B might have height, width and thickness. and so on. The json formatted record is shown below for one po table that actually has 29 attributes/columns that are mostly blacked out. i'm not a json guy but i believe the name to the left of each slash is a section and the name to the right is a specific field contained in that section.
if we can pull this off, we can modify what is generated by the union all by simply changing the biz table metadata.
so if anyone is still with me, has anyone seen custom code that does something like this and is posted publicly somewhere for all to share?
-
I've not heard of anything like that, not that I've looked.
But if you are able to pull together some sample data and desired results, someone here might have a go at coding it for you in T-SQL.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Haven't seen this. I might look at a specialized view (yikes) or proc that normalizes the data from the differently formatted PO tables and then joins those results.
-
thx Phil and Steve.
Phil I wouldnt want to do that to the community since i know how to use the stuff command and xml path. I was being a bit lazy.
This would be funny if xml path was used to generate a dynamic query that generates json path. If we do this I'll post the solution here with some sort of disclaimer.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply