Remove result set line from output

[RickW]

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?

[fpmurphy]

<?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>

[RickW]

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. :)

[fpmurphy]

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.

[RickW]

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

[fpmurphy]

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.

[RickW]

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>

[RickW]

Anybody else can help me out with the template merge?

[RickW]

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!

[fpmurphy]

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>

[RickW]

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