I have this output from a mysql export:

<?xml version="1.0"?>
<resultset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" statement="select product as sample,count(*) as count from table1 where itemno in (5,10,37) and type='food' and package='boxed' group by package,product order by count(*),product desc limit 5 ">
  <row>
    <sample>cereal</sample>
    <count>1</count>
  </row>
  <row>
    <sample>macaroni</sample>
    <count>1</count>
  </row>
  <row>
    <sample>cookies</sample>
    <count>2</count>
  </row>
  <row>
    <sample>rice</sample>
    <count>2</count>
  </row>
  <row>
    <sample>stuffing</sample>
    <count>7</count>
  </row>
</resultset>

I want to use xslt to replace <resultset xmlns:xsi=......> and </resultset> with another tag, like <groceries xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"></groceries>, but without the query statement included.

Suggestions?

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

  <xsl:output method="xml" indent="yes"/>

  <xsl:template match="resultset" >
     <groceries xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
        <xsl:apply-templates select="*"/>
     </groceries>
  </xsl:template>

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

</xsl:stylesheet>

outputs the following:

<?xml version="1.0"?>
<groceries xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
    <sample>cereal</sample>
    <count>1</count>
  </row>
  <row>
    <sample>macaroni</sample>
    <count>1</count>
  </row>
  <row>
    <sample>cookies</sample>
    <count>2</count>
  </row>
  <row>
    <sample>rice</sample>
    <count>2</count>
  </row>
  <row>
    <sample>stuffing</sample>
    <count>7</count>
  </row>
</groceries>

Worked great, thanks!

Can you explain how @*|node() works?

I'm trying to take your template and combine it with this one:

<?xml version="1.0" encoding="ISO-8859-1"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="xml" version="1.0" indent="yes"/>
  <xsl:template match="/resultset">
    <xsl:copy>
    <xsl:copy-of select="@*"/>
      <xsl:for-each select="row">
        <row>
          <xsl:for-each select="field">
            <xsl:element name="{@name}">
              <xsl:value-of select="."/>
            </xsl:element>
          </xsl:for-each>
        </row>
      </xsl:for-each>
    </xsl:copy>
  </xsl:template>
</xsl:stylesheet>

This takes a raw mysql xml export and changes the format a bit. I've been through a lot of online tutorials on the different functions; individually it makes sense, but when I try to combine them into a single xsl it never works properly. Its not clear to me how the logic flows, yet. Thanks for your patience. :)

Can you explain how @*|node() works?

It is called the identity transformation. See this blog post for how to use it to remove or modify namespaces.

Thanks for the link. Can you help me with the template combination? Or only 1 freebie per thread?

You are still thinking in terms of procedural languages - which XSLT is not. Provide a copy of the raw MySQL output and an example of the output you want after the transformation, and I will see what I can do.

Here you go:

<?xml version="1.0"?>

<resultset statement="select tar_host as target,count(*) as count from data_table where dev_id in (5,10,27) and dev_type='firewall' and local_host like 'hr_%' group by local_host,tar_host order by count(*),tar_host desc limit 4" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
        <field name="target">57.65.135.204</field>
        <field name="count">8</field>
  </row>

  <row>
        <field name="target">57.65.135.197</field>
        <field name="count">3</field>
  </row>

  <row>
        <field name="target">74.37.129.63</field>
        <field name="count">1</field>
  </row>

  <row>
        <field name="target">61.142.90.210</field>
        <field name="count">1</field>
  </row>
</resultset>

The resultset statement needs removed like before, and:

<field name="target">57.65.135.204</field>
        <field name="count">8</field>

Needs turned into:

<target>57.65.135.204</target>
        <count>8</count>

Anybody else can help me out with the template merge?

Okay got it:

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

  <xsl:output method="xml" version="1.0" indent="yes"/>
  <xsl:template match="resultset">
     <table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
       <xsl:copy-of select="resultset/@*"/>
       <xsl:for-each select="row">
        <row>
          <xsl:for-each select="field">
            <xsl:element name="{@name}">
              <xsl:value-of select="."/>
            </xsl:element>
          </xsl:for-each>
        </row>
      </xsl:for-each>
     </table>

  </xsl:template>
</xsl:stylesheet>

Thanks for all your help!

Well done!. here is how to do it without for loops

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

  <xsl:output method="xml" version="1.0" indent="yes"/>

  <xsl:template match="resultset">
     <table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
       <xsl:copy-of select="resultset/@*"/>
       <xsl:apply-templates select="row" />
     </table>
  </xsl:template>

  <xsl:template match="row">
     <row>
        <xsl:apply-templates select="field" />
     </row>
  </xsl:template>

  <xsl:template match="field">
     <xsl:element name="{@name}">
        <xsl:value-of select="."/>
     </xsl:element>
  </xsl:template>

</xsl:stylesheet>

What is the advantage of doing it without the loop? Is it more processing efficient?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.