Re: Decomposing xml into table
po 22. 6. 2020 v 20:49 odesílatel Surafel Temesgen 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? https://www.postgresql.org/docs/10/functions-xml.html Regards Pavel Comments? > > regards > > Surafel >
Re: Decomposing xml into table
Surafel Temesgen 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
hey Pavel On Mon, Jun 22, 2020 at 9:59 PM Pavel Stehule 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
út 23. 6. 2020 v 13:59 odesílatel Surafel Temesgen napsal: > hey Pavel > > On Mon, Jun 22, 2020 at 9:59 PM Pavel Stehule > 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
Hey Tom On Mon, Jun 22, 2020 at 10:13 PM Tom Lane 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
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
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
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 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