Thread: Decomposing xml into table

Decomposing xml into table

From
Surafel Temesgen
Date:
In PostgreSQL there are a function table_to_xml to map the table content to xml value but there are no functionality to decompose xml back into table which can be used in system that uses xml for transport only or there are a need to migrate to database system to use database functionality. I propose to have this by extending copy to handle xml format as well because file parsing and tuple formation functions is in there and it also seems to me that implement it without using xml library is simpler

Comments?

regards

Surafel

Re: Decomposing xml into table

From
Pavel Stehule
Date:


po 22. 6. 2020 v 20:49 odesílatel Surafel Temesgen <surafel3000@gmail.com> napsal:
In PostgreSQL there are a function table_to_xml to map the table content to xml value but there are no functionality to decompose xml back into table which can be used in system that uses xml for transport only or there are a need to migrate to database system to use database functionality. I propose to have this by extending copy to handle xml format as well because file parsing and tuple formation functions is in there and it also seems to me that implement it without using xml library is simpler

Did you try the xmltable function?


Regards

Pavel

Comments?

regards

Surafel

Re: Decomposing xml into table

From
Tom Lane
Date:
Surafel Temesgen <surafel3000@gmail.com> writes:
>  In PostgreSQL there are a function table_to_xml to map the table content
> to xml value but there are no functionality to decompose xml back into
> table

Huh?  XMLTABLE does that, and it's even SQL-standard.

> I propose to have this by extending copy to handle xml format as well because
> file parsing and tuple formation functions is in there

Big -1 on that.  COPY is not for general-purpose data transformation.
The more unrelated features we load onto it, the slower it will get,
and probably also the more buggy and unmaintainable.  There's also a
really fundamental mismatch, in that COPY is designed to do row-by-row
processing with essentially no cross-row state.  How would you square
that with the inherently nested nature of XML?

> and it also seems to
> me that implement it without using xml library is simpler

I'm not in favor of implementing our own XML functionality, at least
not unless we go all the way and remove the dependency on libxml2
altogether.  That wouldn't be a terrible idea --- libxml2 has a long
and sad track record of bugs, including security issues.  But it'd be
quite a big job, and it'd still have nothing to do with COPY.

The big-picture question here, though, is why expend effort on XML at all?
It seems like JSON is where it's at these days for that problem space.

            regards, tom lane



Re: Decomposing xml into table

From
Surafel Temesgen
Date:
hey Pavel

On Mon, Jun 22, 2020 at 9:59 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:

Did you try the xmltable function?


yes i know it  but i am proposing changing given xml data in to relational form and insert it to desired table at once

regards
Surafel

Re: Decomposing xml into table

From
Pavel Stehule
Date:


út 23. 6. 2020 v 13:59 odesílatel Surafel Temesgen <surafel3000@gmail.com> napsal:
hey Pavel

On Mon, Jun 22, 2020 at 9:59 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:

Did you try the xmltable function?


yes i know it  but i am proposing changing given xml data in to relational form and insert it to desired table at once

It is a question of how common it is.  Because there is no common format for xml, I agree with Tom, so it should not be part of core. A import from XML can be done with COPY PROGRAM

or some special tools like https://github.com/okbob/pgimportdoc

There is too high variability so some special external tool will be better (more cleaner, more user friendly).

Regards

Pavel



regards
Surafel

Re: Decomposing xml into table

From
Surafel Temesgen
Date:
Hey Tom

On Mon, Jun 22, 2020 at 10:13 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Big -1 on that.  COPY is not for general-purpose data transformation.
The more unrelated features we load onto it, the slower it will get,
and probably also the more buggy and unmaintainable. 

what new format handling takes to add regards to performance is a check to a few place and I don’t think that have noticeable performance impact and as far as I can see copy is extendable by design and I don’t think adding additional format will be a huge undertaking

 
There's also a
really fundamental mismatch, in that COPY is designed to do row-by-row
processing with essentially no cross-row state.  How would you square
that with the inherently nested nature of XML?


In xml case the difference is row delimiter . In xml mode user specifies row delimiter tag name and starting from start tag of specified name up to its end tag treated as single row and every text content in between will be our columns value filed

 

The big-picture question here, though, is why expend effort on XML at all?
It seems like JSON is where it's at these days for that problem space.

there are a legacy systems and I think xml is still popular

regards
Surafel  

Re: Decomposing xml into table

From
Thomas Kellerer
Date:
Surafel Temesgen schrieb am 23.06.2020 um 13:59:
>> Did you try the xmltable function?
>
> yes i know it  but i am proposing changing given xml data in to
> relational form and insert it to desired table at once
Well, xmltable() does change the XML data to a relational form and
the result can directly be used to insert into a table

   insert into target_table (...)
   select ...
   from xmltable(...);




Re: Decomposing xml into table

From
Andreas Karlsson
Date:
On 6/22/20 8:49 PM, Surafel Temesgen wrote:
> Comments?

I feel it would make more sense to add features like this to an external 
tool, e.g pgloader. But even if we add it to the core PostgreSQL project 
I feel the XML parsing should be done in the client, not in the database 
server. The COPY command is already very complex.

Andreas



Re: Decomposing xml into table

From
Chapman Flack
Date:
On 06/23/20 08:57, Thomas Kellerer wrote:
> Surafel Temesgen schrieb am 23.06.2020 um 13:59:
>>> Did you try the xmltable function?
>>
>> yes i know it  but i am proposing changing given xml data in to
>> relational form and insert it to desired table at once
> Well, xmltable() does change the XML data to a relational form and
> the result can directly be used to insert into a table
> 
>    insert into target_table (...)
>    select ...
>    from xmltable(...);


The use case that I imagine might be driving this would be where the XML
source is not deeply or elaborately nested, but is yuuge. In such a case,
PostgreSQL's XML handling and xmltable will not be doing beautiful things:

- the data coming from the frontend will have to be completely buffered
  in backend memory, and then parsed as XML once by the XML data type
  input routine, only for the purpose of confirming it's XML. The unparsed
  form is what becomes the Datum value, which means

- xmltable gets to parse it a second time, again all in memory, and then
  generate the set-returning function result tuples from it.

- as I last understood it [1], even the tuples generated as a result
  get all piled up in a tuplestore before the next part of the (what
  you would otherwise hope to call) "pipeline" can happen. (There may
  be work on better pipelining that part.)

So I would say for that use case, it will be hard to do better than an
external process acting as a filter from XML in to COPY-formatted tuples
out.

The XML-processing library I'm most familiar with, Saxon, can do some
sophisticated analysis of an XML Query or XSLT transformation and
determine when it can be done while consuming the XML in streaming
mode rather than building a complete tree first. (The open-source
"community edition" doesn't have that trick, only the paid editions,
but they're otherwise compatible, so you can prototype stuff using
the community edition, and then drop in a paid version and poof, it
goes faster.)


On 06/23/20 08:25, Surafel Temesgen wrote:
> On Mon, Jun 22, 2020 at 10:13 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> The big-picture question here, though, is why expend effort on XML
>> at all?
>> It seems like JSON is where it's at these days for that problem space.
>
> there are a legacy systems and I think xml is still popular

I had an interesting conversation about that at PGCon a year ago, with
someone who crystallized this idea better than I had at the time (but
may or may not want his name on it):

We tend to repeat a cycle of: a new technology is introduced, minimal
at first, then develops a good ecosystem of sophisticated tooling, then
looks complicated and gets replaced with something minimal that needs to
repeat the same process.

By this point, we're on to 3.x versions of XML Query, XPath, and XSLT,
very mature languages that can express sophisticated transformations
and optimize the daylights out of them.

JSON now has JSONPATH, which is coming along, and relearning the lessons
of XPath and XQuery, and by the time it has, there will be something else
that's appealing because it looks more minimal, and we'll be having the
"why expend effort on JSON at all?" conversation.


Regards,
-Chap


[1] https://www.postgresql.org/message-id/12389.1563746057%40sss.pgh.pa.us