jsugarelli / xml2relational Goto Github PK

Converting XML documents into relational data models

R 100.00%

r-package xml xml-serialization relational-database relational-model sql

xml2relational's Introduction

About me

I am an economist, self-taught programmer, BI lead, author, lecturer and R enthusiast. I enjoy programming, writing and teaching. Focus of my work is on making complex things more accessible.

My GitHub presence features the R packages I have developed over the last couple of years. They broadly fall into the following categories:

Statistical methods and teaching (packages quantification and xplain)
Data import and export (packages flatxml, xml2relational, xmlconvert, refreshr)
Working more efficiently with R (packages packagefinder and debugr)
Enhancing the R language (packages pointr, switchcase, typehint)
Shiny (shinyfilter)

My blog

I run the Topics in R blog which I mainly use for announcements (new packages, articles, books) and which also feeds into RBloggers. Here are the most recent posts (at least the major ones):

Twitter

I try to keep away from social media (Github != social_media) to protect my time and be more productive, but I do use Twitter. If you want to receive the latest updates on my packages and learn about interesting things I come across, click @jsugarelli to follow.

Current projects

Reviewing the English translation of my German Python & JavaScript introductory textbook (publisher: Springer)
Giving lectures in economics at Hochschule München

Languages & Tools

Languages

Mainly

but also from time to time

Generally, I like to try out new (or old?) things (ever written something in FORTH?)

Tools

For the tool freaks out there: My editor/IDE choice is quite language-dependent. I work mainly in R Studio (for R), Microsoft SQL Server Management Studio (for T-SQL), Heidi (for MySQL), and DBeaver (for Postgres and everything else). I also use Sublime Text (mainly for JavaScript, Perl and PHP code) as well as PyCharm (for Python). To organize everything I work with Microsoft To Do and Microsoft OneNote (my second brain, absolutely indispensable). And most importantly for efficiency and avoidance of frictions: I use the same tools at home and in my job.

Stats

xml2relational's People

Contributors

Stargazers

Watchers

Forkers

zxexz davisvaughan nimafazeli

xml2relational's Issues

Not doing much...

I probably made a mistake, but how is this supposed to work?

I followed the instructions in the docs and tried to run customer.data <- toRelational("file.xml")
on this file:

https://raw.githubusercontent.com/gramps-project/gramps/maintenance/gramps51/example/gramps/data.gramps

it ran for over an hour and generated no output while hogging one core at 100%.

Please advise, is the file maybe too big?

Child items do not have a foreign key pointing to the parent

Here is a minimum reproducible example:
consider the mre.xml file (this is a modified version of an xbrl file that I cropped ):

 <XML>
  <ROCRA>
    <RAN contextRef="m1">Moody's Investors Service</RAN>
    <FCD contextRef="m1">2022-04-15</FCD>
    <OD>
      <OSC contextRef="m1">Financial</OSC>
      <OBNAME contextRef="m1">MUFG Securities (Europe) N.V.</OBNAME>
      <OI contextRef="m1">830986287</OI>
      <OIS contextRef="m1">NRSRO</OIS>
      <ORD>
        <IP contextRef="m1">true</IP>
        <R contextRef="m1">A1</R>
        <RAD contextRef="m1">2018-10-25</RAD>
        <RAC contextRef="m1">NW</RAC>
        <RT contextRef="m1">Organization</RT>
        <RTT contextRef="m1">LT Issuer Rating</RTT>
      </ORD>
      <ORD>
        <IP contextRef="m1">true</IP>
        <R contextRef="m1">B1</R>
        <RAD contextRef="m1">2019-10-21</RAD>
        <RAC contextRef="m1">NW</RAC>
        <RT contextRef="m1">Organization</RT>
        <RTT contextRef="m1">LT Issuer Rating</RTT>
      </ORD>
    </OD>
    <ISD>
      <SSC contextRef="m1">Financial</SSC>
      <ISSNAME contextRef="m1">MUFG Securities (Europe) N.V.</ISSNAME>
      <ISI contextRef="m1">830986287</ISI>
      <ISIS contextRef="m1">NRSRO</ISIS>
    </ISD>
  </ROCRA>
  </XML>

If I run mre.xml %>% toRelational() I get the following:

$XML
  ID_XML FKID_ROCRA
1  13320       5294

$ROCRA
  ID_ROCRA                       RAN        FCD FKID_OD FKID_ISD
1     5294 Moody's Investors Service 2022-04-15  947669    90193

$OD
   ID_OD       OSC                        OBNAME        OI   OIS FKID_ORD
1 947669 Financial MUFG Securities (Europe) N.V. 830986287 NRSRO   103000

$ORD
  ID_ORD   IP  R        RAD RAC           RT              RTT
1 182003 true A1 2018-10-25  NW Organization LT Issuer Rating
2 103000 true B1 2019-10-21  NW Organization LT Issuer Rating

$ISD
  ID_ISD       SSC                       ISSNAME       ISI  ISIS
1  90193 Financial MUFG Securities (Europe) N.V. 830986287 NRSRO

I want to join the OD and ORD tables to create a dataframe with two rows from the ORD table joined with the relevant information from the OD table, i.e. the following:

        OSC                        OBNAME        OI   OIS   IP  R        RAD RAC           RT              RTT
1 Financial MUFG Securities (Europe) N.V. 830986287 NRSRO true A1 2018-10-25  NW Organization LT Issuer Rating
2 Financial MUFG Securities (Europe) N.V. 830986287 NRSRO true B1 2019-10-21  NW Organization LT Issuer Rating

I admit I am not very experienced in relational databases, but I guess logically I should be able to get this by matching a foreign key from the ORD table that points to a primary key in the OD table (i.e. the parent node) and not the other way round. But instead, it is the parent table that is giving one single key that matches with only one of its children.

Thanks

Empty tag can cause toRelational() fcn to error with "Can't convert `replace` <character> to match type of `data` <logical>"

Thanks for this nice package!

I discovered an issue where an empty tag (see below) can cause toRelational() to fail, see reproducible example below:

# this xml fragment has an empty title tag (nested under relatedItem)
doc_err <- '<xml>
  <titleInfo lang="eng"><title>CreativeBot</title></titleInfo>          
  <relatedItem type="host">
    <titleInfo><title></title></titleInfo>
  </relatedItem>
</xml>
'

# this statement gives an error
doc_err |> xml2relational::toRelational()

# Error in `vec_assign()`:
# ! Can't convert `replace` <character> to match type of `data` <logical>.
# Run `rlang::last_error()` to see where the error occurred.

# this xml fragment is equal to the previous apart except for using a space instead inside the title tag (nested under relatedItem)
doc_ok <- '<xml>
  <titleInfo lang="eng"><title>CreativeBot</title></titleInfo>          
  <relatedItem type="host">
    <titleInfo><title> </title></titleInfo>
  </relatedItem>
</xml>
'

# this statement works ok
doc_ok |> xml2relational::toRelational()

I think https://github.com/jsugarelli/xml2relational/blob/master/R/xml2relational.r#L105 is what is triggering the error.