xml - 使用 XSL 转换 XML 以获取复制的值以导入 SQL

Question

我有一个这样的 XML 文件：

<?xml version="1.0" encoding="UTF-8"?>
<day-models>
    <day-model>
      <deleted type="boolean">false</deleted>
      <color type="integer">16777215</color>
      <contents type="array">
        <content>
          <day-model-content-id type="integer">1483</day-model-content-id>
          <deleted type="boolean">false</deleted>
          <start-date type="date">1900-01-01</start-date>
          <end-date type="date">4000-01-01</end-date>
          <start-time type="dateTime">1901-01-01T09:00:00Z</start-time>
          <end-time type="dateTime">1901-01-01T17:00:00Z</end-time>
          <interval nil="true"/>
          <items type="array">
            <item>
              <activity-id type="integer">1050</activity-id>
              <type type="symbol">activity</type>
              <absolute-positioned type="boolean">false</absolute-positioned>
              <start-time type="dateTime">1901-01-01T00:00:00Z</start-time>
              <end-time type="dateTime">1901-01-01T08:00:00Z</end-time>
            </item>
            <item>
              <activity-id type="integer">1050</activity-id>
              <type type="symbol">background_activity</type>
              <absolute-positioned type="boolean">false</absolute-positioned>
              <start-time type="dateTime">1901-01-01T00:00:00Z</start-time>
              <end-time type="dateTime">1901-01-01T08:00:00Z</end-time>
            </item>
          </items>
        </content>
      </contents>
      <day-model-id type="integer">1476</day-model-id>
      <name>Rota'd Day Off</name>
      <name-short>RDO</name-short>
      <type type="symbol">standard</type>
    </day-model>
</day-models>

我需要将其扩展为这样的输出以导入 SQL：

<?xml version="1.0" encoding="UTF-8"?>
<day-models>
    <day-model>
      <day-model-deleted>false</day-model-deleted>
      <day-model-color>16777215</day-model-color>
      <day-model-id>1476</day-model-id>
      <day-model-name>Rota'd Day Off</day-model-name>
      <day-model-name-short>RDO</day-model-name-short>
      <day-model-type>standard</day-model-type>
      <day-model-content-id>1483</day-model-content-id>
      <day-model-content-deleted>false</day-model-content-deleted>
      <day-model-content-valid-from-date>1900-01-01</day-model-content-valid-from-date>
      <day-model-content-valid-to-date>4000-01-01</day-model-content-valid-to-date>
      <day-model-content-start-time>1901-01-01T09:00:00Z</day-model-content-start-time>
      <day-model-content-end-time>1901-01-01T17:00:00Z</day-model-content-end-time>
      <day-model-content-interval/>
      <day-model-content-activity-id>1050</day-model-content-activity-id>
      <day-model-content-activity-type>activity</day-model-content-activity-type>
      <day-model-content-activity-absolute-positioned>false</day-model-content-activity-absolute-positioned>
      <day-model-content-activity-start-time>1901-01-01T00:00:00Z</day-model-content-activity-start-time>
      <day-model-content-activity-end-time>1901-01-01T08:00:00Z</day-model-content-activity-end-time>
    </day-model>
    <day-model>
      <day-model-deleted>false</day-model-deleted>
      <day-model-color>16777215</day-model-color>
      <day-model-id>1476</day-model-id>
      <day-model-name>Rota'd Day Off</day-model-name>
      <day-model-name-short>RDO</day-model-name-short>
      <day-model-type>standard</day-model-type>
      <day-model-content-id>1483</day-model-content-id>
      <day-model-content-deleted>false</day-model-content-deleted>
      <day-model-content-valid-from-date>1900-01-01</day-model-content-valid-from-date>
      <day-model-content-valid-to-date>4000-01-01</day-model-content-valid-to-date>
      <day-model-content-start-time>1901-01-01T09:00:00Z</day-model-content-start-time>
      <day-model-content-end-time>1901-01-01T17:00:00Z</day-model-content-end-time>
      <day-model-content-interval/>
      <day-model-content-activity-id>1050</day-model-content-activity-id>
      <day-model-content-activity-type>background_activity</day-model-content-activity-type>
      <day-model-content-activity-absolute-positioned>false</day-model-content-activity-absolute-positioned>
      <day-model-content-activity-start-time>1901-01-01T00:00:00Z</day-model-content-activity-start-time>
      <day-model-content-activity-end-time>1901-01-01T08:00:00Z</day-model-content-activity-end-time>
    </day-model>
</day-models>

我目前对 XSL 的尝试是这样的：

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:strip-space elements="*" />
    <xsl:output method="xml" encoding="UTF-8" indent="yes" omit-xml-declaration="no"/>

    <xsl:template match="@* | node()">
        <xsl:copy>
            <xsl:apply-templates select="@* | node()"/>
        </xsl:copy>
    </xsl:template>

    <xsl:template match="@*"/>

    <xsl:template match="item">
        <xsl:copy>
            <day-model-content-id><xsl:value-of select="../../day-model-content-id"/></day-model-content-id>
            <day-model-content-deleted><xsl:value-of select="../../deleted"/></day-model-content-deleted>
            <day-model-content-valid-from-date><xsl:value-of select="../../start-date"/></day-model-content-valid-from-date>
            <day-model-content-valid-to-date><xsl:value-of select="../../end-date"/></day-model-content-valid-to-date>
            <day-model-content-start-time><xsl:value-of select="../../start-time"/></day-model-content-start-time>
            <day-model-content-end-time><xsl:value-of select="../../end-time"/></day-model-content-end-time>
            <day-model-content-interval><xsl:value-of select="../../interval"/></day-model-content-interval>
            <day-model-content-activity-id><xsl:value-of select="activity-id"/></day-model-content-activity-id>
            <day-model-content-activity-type><xsl:value-of select="type"/></day-model-content-activity-type>
            <day-model-content-activity-absolute-positioned><xsl:value-of select="absolute-positioned"/></day-model-content-activity-absolute-positioned>
            <day-model-content-activity-start-time><xsl:value-of select="start-time"/></day-model-content-activity-start-time>
            <day-model-content-activity-end-time><xsl:value-of select="end-time"/></day-model-content-activity-end-time>
        </xsl:copy>
    </xsl:template>

    <xsl:template match="content/day-model-content-id"/>
    <xsl:template match="content/deleted"/>
    <xsl:template match="content/start-date"/>
    <xsl:template match="content/end-date"/>
    <xsl:template match="content/start-time"/>
    <xsl:template match="content/end-time"/>
    <xsl:template match="content/interval"/>

    <xsl:template match="contents">
        <xsl:apply-templates select="@* | node()"/>
    </xsl:template>

    <xsl:template match="content">
        <xsl:apply-templates select="@* | node()"/>
    </xsl:template>
</xsl:stylesheet>

但这给了我：

    <?xml version="1.0" encoding="UTF-8"?>
<day-models>
    <day-model>
      <deleted>false</deleted>
      <color>16777215</color>
      <items>
        <item>
          <day-model-content-id>1483</day-model-content-id>
          <day-model-content-deleted>false</day-model-content-deleted>
          <day-model-content-valid-from-date>1900-01-01</day-model-content-valid-from-date>
          <day-model-content-valid-to-date>4000-01-01</day-model-content-valid-to-date>
          <day-model-content-start-time>1901-01-01T09:00:00Z</day-model-content-start-time>
          <day-model-content-end-time>1901-01-01T17:00:00Z</day-model-content-end-time>
          <day-model-content-interval/>
          <day-model-content-activity-id>1050</day-model-content-activity-id>
          <day-model-content-activity-type>activity</day-model-content-activity-type>
          <day-model-content-activity-absolute-positioned>false</day-model-content-activity-absolute-positioned>
          <day-model-content-activity-start-time>1901-01-01T00:00:00Z</day-model-content-activity-start-time>
          <day-model-content-activity-end-time>1901-01-01T08:00:00Z</day-model-content-activity-end-time>
        </item>
        <item>
          <day-model-content-id>1483</day-model-content-id>
          <day-model-content-deleted>false</day-model-content-deleted>
          <day-model-content-valid-from-date>1900-01-01</day-model-content-valid-from-date>
          <day-model-content-valid-to-date>4000-01-01</day-model-content-valid-to-date>
          <day-model-content-start-time>1901-01-01T09:00:00Z</day-model-content-start-time>
          <day-model-content-end-time>1901-01-01T17:00:00Z</day-model-content-end-time>
          <day-model-content-interval/>
          <day-model-content-activity-id>1050</day-model-content-activity-id>
          <day-model-content-activity-type>background_activity</day-model-content-activity-type>
          <day-model-content-activity-absolute-positioned>false</day-model-content-activity-absolute-positioned>
          <day-model-content-activity-start-time>1901-01-01T00:00:00Z</day-model-content-activity-start-time>
          <day-model-content-activity-end-time>1901-01-01T08:00:00Z</day-model-content-activity-end-time>
        </item>
      </items>
      <day-model-id>1476</day-model-id>
      <name>Rota'd Day Off</name>
      <name-short>RDO</name-short>
      <type>standard</type>
    </day-model>
</day-models>

我最挣扎的部分是day-model从之后获取项目contents并将它们包含在每个项目的最终输出中。

我对替代方案持开放态度——我考虑将输出拆分为更小的文件（例如day-model一个、一个day-model-content和day-model-content-activity一个），但我还认为在将文件导入临时表后我可以在 SQL 中执行此工作负载。

非常感谢任何帮助。

标签： xmlxslt

Answer 1

我会建议一种不同的方法：

XSLT 1.0

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:template match="/day-models">
    <xsl:copy>
        <xsl:for-each select="day-model">
            <xsl:variable name="day-model-properties">
                <day-model-deleted>
                    <xsl:value-of select="deleted"/>
                </day-model-deleted>
                <day-model-color>
                    <xsl:value-of select="color"/>
                </day-model-color>
                <day-model-id>
                    <xsl:value-of select="day-model-id"/>
                </day-model-id>
                <day-model-name>
                    <xsl:value-of select="name"/>       
                </day-model-name>
                <day-model-name-short>
                    <xsl:value-of select="name-short"/>     
                </day-model-name-short>
                <day-model-type>
                    <xsl:value-of select="type"/>               
                </day-model-type>   
            </xsl:variable>
            <xsl:for-each select="contents/content">
                <xsl:variable name="content-properties">
                    <day-model-content-id>
                        <xsl:value-of select="day-model-content-id"/>   
                    </day-model-content-id>
                    <day-model-content-deleted>
                        <xsl:value-of select="deleted"/>
                    </day-model-content-deleted>
                    <day-model-content-valid-from-date>
                        <xsl:value-of select="start-date"/> 
                    </day-model-content-valid-from-date>
                    <day-model-content-valid-to-date>
                        <xsl:value-of select="end-date"/>   
                    </day-model-content-valid-to-date>
                    <day-model-content-start-time>
                        <xsl:value-of select="start-time"/> 
                    </day-model-content-start-time>
                    <day-model-content-end-time>
                        <xsl:value-of select="end-time"/>   
                    </day-model-content-end-time>
                    <day-model-content-interval>
                        <xsl:value-of select="interval"/>   
                    </day-model-content-interval>
                </xsl:variable>
                <xsl:for-each select="items/item">
                    <day-model>
                        <xsl:copy-of select="$day-model-properties"/>
                        <xsl:copy-of select="$content-properties"/>
                        <day-model-content-activity-id>
                            <xsl:value-of select="activity-id"/>
                        </day-model-content-activity-id>
                        <day-model-content-activity-type>
                            <xsl:value-of select="type"/>
                        </day-model-content-activity-type>
                        <day-model-content-activity-absolute-positioned>
                            <xsl:value-of select="absolute-positioned"/>
                        </day-model-content-activity-absolute-positioned>
                        <day-model-content-activity-start-time>
                            <xsl:value-of select="start-time"/>
                        </day-model-content-activity-start-time>
                        <day-model-content-activity-end-time>
                            <xsl:value-of select="end-time"/>
                        </day-model-content-activity-end-time>
                    </day-model>
                </xsl:for-each>
            </xsl:for-each>
        </xsl:for-each>
    </xsl:copy>
</xsl:template>

</xsl:stylesheet>