Oxygen XML Forum

Posted: **Wed Feb 05, 2020 2:28 am**

Hello Guys,

I'm kind of new to the wonderfull world of XML so please be gentle if i put this in the wrong forum.

At the moment I try to do a database import and a problem occured which I can't solve with my skills at the moment. I have over 10.000 tables that must contain 5 topic columns as following:

<table name="biblio">
<column name="topic1">Topic</column>
<column name="topic2">Topic</column>
<column name="topic3">Topic</column>
<column name="topic4">Topic</column>
<column name="topic5">Topic</column>
</table>

The problem is that the data i have don not always have 5 topic-columns. Sometimes they only have the first one, sometimes they only have the first 3 and sometimes they don't even have any. Is there a way to add the missing topic columns only if they are missing without replacing the existing ones?

Posted: **Wed Feb 05, 2020 9:53 pm**

Within XSLT or XQuery you can certainly check e.g. whether the table element has certain column child elements with a particular name attribute. And you can create a new result document where you add elements that don't exist in the input. For XQuery there is also XQuery update that can manipulate existing documents in the database.

It is, however, not clear in which context you are dealing with XML, as you mention tables it appears you are dealing with a relational db system.

Posted: **Thu Feb 06, 2020 6:58 pm**

Hello Martin, thank you for responding.

Martin Honnen wrote: ↑Wed Feb 05, 2020 9:53 pm It is, however, not clear in which context you are dealing with XML, as you mention tables it appears you are dealing with a relational db system.

I have a local XML-File which I want to use for an SQL-Database Import via phpmyadmin. I made a lot of changes to the original file to match the Database requirements, which worked fine till this Problem occured which is way over my head.

The desired output should look like this:

Code: Select all

<database name="DATABASE">
<table name="biblio">
.
.
.
<column name="topic1">Topicfield1</column>
<column name="topic2">Topicfield2</column>
<column name="topic3">Topicfield3</column>
<column name="topic4">Topicfield2</column>
<column name="topic5">Topicfield3</column>
</table>

The table has a lot of other column's, which hopefully don't matter at this point. The problem now is, that not all of those tables have these columns - but must have them for the database import - otherwise the databse will break and don't show any of these topic fields. So i need a solution which creates those topic fields in tables where they do not exist while keeping the topic fields if they do. Some tables include no topic fields, some do topic1, some do topic1 and topic2 and so on.

I think a pseudo code would look like:

Code: Select all

if (doesn't exists(/table/column/@topic1))
	then insert <column name="topic1"></column>
else if (doesn't exists(/table/column/@topic2
	then insert <column name="topic2"></column>
else if (doesn't exists(/table/column/@topic3)
	then insert <column name="topic3"></column>
	else if (doesn't exists(/table/column/@topic4)
	then insert <column name="topic4"></column>
	else if (doesn't exists(/table/column/@topic5)
	then insert <column name="topic5"></column>

But as I said, i have very little to no experience about XSLT/Xquery. I would be very thankful for everything that wors, and even more for an explaination of the solution to learn something from it.

Posted: **Fri Feb 07, 2020 10:22 am**

It kind of matters which other columns can exist or do always exist as we some way to identify the place where to add the topic columns; assuming for instance you want them inserted at the end of all child elements you could use

Code: Select all

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
	xmlns:xs="http://www.w3.org/2001/XMLSchema"
	exclude-result-prefixes="#all"
	version="3.0">

  <xsl:mode on-no-match="shallow-copy"/>
  
  <xsl:output indent="yes"/>

  <xsl:param name="topic-cols" as="xs:string*"
    select="'topic1', 'topic2', 'topic3', 'topic4', 'topic5'"/>

  <xsl:template match="table[@name = 'biblio']">
      <xsl:copy>
          <xsl:apply-templates select="@*"/>
          <xsl:apply-templates select="column[not(@name = $topic-cols)]"/>
          <xsl:variable name="table" select="."/>
          <xsl:for-each select="$topic-cols">
              <xsl:variable name="col" select="$table/column[@name = current()]"/>
              <xsl:choose>
                  <xsl:when test="$col">
                      <xsl:apply-templates select="$col"/>
                  </xsl:when>
                  <xsl:otherwise>
                      <column name="{.}"></column>
                  </xsl:otherwise>
              </xsl:choose>
          </xsl:for-each>
      </xsl:copy>
  </xsl:template>
  
</xsl:stylesheet>

That is XSLT 3 that oXygen can execute with Saxon 9.8 or 9.9. You can also run it online at https://xsltfiddle.liberty-development.net/6rexji7/3.

The possible topic column names are passed in as a global parameter of sequence type xs:string* (i.e. a list of zero or more strings). the base stylesheet processing is set up by the xsl:mode declaration doing an identity transformation, i.e. shallow-copy level by level recursively to allow us to add more specific templates for any non-copy changes we want to make; that is done with the template for the table named biblio where we also make a shallow copy for the table, delegate processing of any attributes and any non topic columns to the existing identity transformation and then implement the checks for the existence of any of the topic columns.

Oxygen XML Forum

Creating Child-Nodes only if they do not already exist

Creating Child-Nodes only if they do not already exist

Re: Creating Child-Nodes only if they do not already exist

Re: Creating Child-Nodes only if they do not already exist

Re: Creating Child-Nodes only if they do not already exist