Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

Transforming XML to XML with XSLT

I have been trying to get a certain xml document into a format that would be more easily digested by my xml task in SSIS. The format I am receiving seems to have too many layers to easily extract the information I need. I have worked with xslt transformation before but not in this way. I am having problems getting it to loop properly through the nodes. Yes I have "googled" my way through the interwebs looking for examples that will help with this but have found nothing that works. So I thought I would see what "the experts" say.

Here is the XML I get:

<?xml version="1.0" encoding="ISO-8859-1" ?>
<companies_report>
     <companies>
          <company id="1">
                <active>0</active>
                <hide>0</hide>
                <description>Test Company</description>
                <symbol>TEST</symbol>
                <security id="100">
                     <active>0</active>
                     <display_symbol>TEST</display_symbol>
                     <description>Test Company</description>
                </security>
          </company>
          <company id="2">
                <active>1</active>
                <hide>0</hide>
                <description>Sample Company</description>
                <symbol>SAMP</symbol>
                <security id="200">
                     <active>1</active>
                     <display_symbol>SAMP1</display_symbol>
                     <description>Sample Company 1</description>
                </security>
                <security id="201">
                     <active>1</active>
                     <display_symbol>SAMP2</display_symbol>
                     <description>Sample Company 2</description>
                </security>
          </company>
     </companies>
</companies_report>

Open in new window


And here is what I want:

<securities>
     <security>
          <id>100</>
           <security_active>0</security_active> ---From the security child node
           <display_symbol>TEST</display_symbol>
           <description>Test Company</description>
           <company_id>1</company_id>
           <company_hide>0</company_hide>
          <company_active>0</company_active>
     </security>
     <security>
          <id>200</>
           <active>1</active> ---From the security child node
           <display_symbol>SAMP1</display_symbol>
           <description>Sample Company 1</description>
           <company_id>2</company_id>
          <company_active>1</company_active>
           <company_hide>0</company_hide>
     </security>
     <security>
          <id>201</>
           <active>1</active> ---From the security child node
           <display_symbol>SAMP2</display_symbol>
           <description>Sample Company 2</description>
           <company_id>2</company_id>
          <company_active>1</company_active>
           <company_hide>0</company_hide>
     </security>
</securities>

Open in new window


Here is my XSLT:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes"/>   
     <xsl:template match="/companies_report"> 
          <securities language="en">
               <xsl:for-each select="/companies_report/companies/company">
               <security>
                    <security_id><xsl:value-of select="/companies_report/companies/company/security/@id"/></security_id>
                    <security_active><xsl:value-of select="/companies_report/companies/company/security/active"/></security_active>
                    <display_symbol><xsl:value-of select="/companies_report/companies/company/security/display_symbol"/></display_symbol>
                    <description><xsl:value-of select="/companies_report/companies/company/security/description"/></description>
                    <company_id><xsl:value-of select="/companies_report/companies/company/@id"/></company_id>
                    <company_hide><xsl:value-of select="/companies_report/companies/company/hide"/></company_hide> 
                    <company_active><xsl:value-of select="/companies_report/companies/company/active"/></company_active>               
                </security>
                </xsl:for-each>
          </securities>
    </xsl:template>
</xsl:stylesheet>

Open in new window


And here is what I get:
<?xml version="1.0" encoding="utf-8"?>
<securities language="en">
     <security>
          <security_id>100</security_id>
          <security_active>0</security_active>
          <display_symbol>TEST</display_symbol>
          <description>Test Company</description>
          <company_id>1</company_id>
          <company_hide>0</company_hide>
          <company_active>0</company_active>
     </security>
     <security>
          <security_id>100</security_id>
          <security_active>0</security_active>
          <display_symbol>TEST</display_symbol>
          <description>Test Company</description>
          <company_id>1</company_id>
          <company_hide>0</company_hide>
          <company_active>0</company_active>
     </security>
</securities>

Open in new window


So obviously I do not have the for each statement configured correctly or something else that I am not seeing because it is only reading the first node and repeating it. Help would be greatly appreciated. Thanks.
0
Russell Scheinberg, MCSE Data Platform 2012
Asked:
Russell Scheinberg, MCSE Data Platform 2012
  • 4
  • 4
  • 2
2 Solutions
 
mccarlIT Business Systems Analyst / Software DeveloperCommented:
Yes, both the select in your for-each is not quite specific and then each of the selects in your value-of's in NOT based on the current context (ie. the current element of the for-each loop). Try the following...
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes"/>   
     <xsl:template match="/companies_report"> 
          <securities language="en">
               <xsl:for-each select="/companies_report/companies/company/security">
               <security>
                    <security_id><xsl:value-of select="@id"/></security_id>
                    <security_active><xsl:value-of select="active"/></security_active>
                    <display_symbol><xsl:value-of select="display_symbol"/></display_symbol>
                    <description><xsl:value-of select="description"/></description>
                    <company_id><xsl:value-of select="../@id"/></company_id>
                    <company_hide><xsl:value-of select="../hide"/></company_hide> 
                    <company_active><xsl:value-of select="../active"/></company_active>               
                </security>
                </xsl:for-each>
          </securities>
    </xsl:template>
</xsl:stylesheet>

Open in new window

0
 
Geert BormansCommented:
1. Inside a template or inside a for-each, the context is the context of the current node

1.a. So inside the
<xsl:template match="/companies_report">
tags, you can consider yourself being IN the companies_report context
you should do
<xsl:for-each select="companies/company"> (relative)
instead of
<xsl:for-each select="/companies_report/companies/company"> (absolute starting from root)

1.b. You want to reproduce all security elements, not all company elements, so your for-each should dig deeper
you should do
<xsl:for-each select="companies/company/security"> (relative)
instead of
<xsl:for-each select="/companies_report/companies/company"> (absolute starting from root)
(note that '/' at the beginning makes you start from the root)

1.c So inside the new <xsl:for-each select="companies/company/security">
you can consider yourself inside the security element
and you should do
<security_id><xsl:value-of select="@id"/></security_id>
instead of
<security_id><xsl:value-of select="/companies_report/companies/company/security/@id"/></security_id>
The way you are doing it now is wrong in a somewhat subtle way. Because you do the value-of select from the root,
you will select all /companies_report/companies/company/security/@id in the document
so you basically select all three id attributes but only show the first one using value-of

1.d If you are reconstructing an element completely, I would use xsl:copy-of
<description><xsl:value-of select="description"/></description>
to become
<xsl:copy-of select="description"/>

1.e if you are inside a security element, and you need something from the company... you can address into the parent using '../'
like this
<company_hide><xsl:value-of select="../hide"/></company_hide>

Bringing all of this together, we will end up with a stylesheet that is very close to what mccarl did
(consider this the annotation of mcarls work)
Try to understand this, because step two is getting interesting

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="xml" indent="yes"/>   
    <xsl:template match="/companies_report"> 
        <securities language="en">
            <xsl:for-each select="companies/company/security">
                <security>
                    <security_id><xsl:value-of select="@id"/></security_id>
                    <security_active><xsl:value-of select="active"/></security_active>
                    <xsl:copy-of select="display_symbol"/>
                    <xsl:copy-of select="description"/>
                    <company_id><xsl:value-of select="../@id"/></company_id>
                    <company_hide><xsl:value-of select="../hide"/></company_hide> 
                    <company_active><xsl:value-of select="../active"/></company_active>               
                </security>
            </xsl:for-each>
        </securities>
    </xsl:template>
</xsl:stylesheet>

Open in new window

0
 
Geert BormansCommented:
2. When doing XSLT you should avoid for-each as much as possible.
There is a technique that makes your stylesheets a lot easier to develop and maintain
xsl:apply-templates basically takes a set of nodes and tells them
"go and find yourself a matching template"
the nodes will then pick a template with a match attribute that matches closest to the node description
A bit vague maybe but this is what it does to the stylesheet

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="xml" indent="yes"/>   
    <xsl:template match="/companies_report"> 
        <securities language="en">
            <xsl:apply-templates select="companies/company/security"/>
        </securities>
    </xsl:template>
    
    <xsl:template match="security">
        <security>
            <security_id><xsl:value-of select="@id"/></security_id>
            <security_active><xsl:value-of select="active"/></security_active>
            <xsl:copy-of select="display_symbol"/>
            <xsl:copy-of select="description"/>
            <company_id><xsl:value-of select="../@id"/></company_id>
            <company_hide><xsl:value-of select="../hide"/></company_hide> 
            <company_active><xsl:value-of select="../active"/></company_active>               
        </security>
    </xsl:template>
    
</xsl:stylesheet>

Open in new window


Try to understand what I am doing here. If you need more explanation about it, please ask.
I decided to step into this question because apply-templates is the most important aspect of programming XSLT and is too often overlooked by beginning developers
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Geert BormansCommented:
3. I usually take that technique a bit further.
Here is how your stylesheet would become if I were to use it in a project

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="xml" indent="yes"/>
    
    <xsl:template match="/">
        <xsl:apply-templates select="companies_report"/>
    </xsl:template>
    
    <xsl:template match="companies_report"> 
        <securities language="en">
            <xsl:apply-templates select="companies/company/security"/>
        </securities>
    </xsl:template>
    
    <xsl:template match="security">
        <security>
            <security_id><xsl:value-of select="@id"/></security_id>
            <security_active><xsl:value-of select="active"/></security_active>
             <xsl:apply-templates select="display_symbol"/>
            <xsl:apply-templates select="description"/>
            <company_id><xsl:value-of select="../@id"/></company_id>
            <company_hide><xsl:value-of select="../hide"/></company_hide> 
            <company_active><xsl:value-of select="../active"/></company_active>               
        </security>
    </xsl:template>
    
    <xsl:template match="node()">
        <xsl:copy>
            <xsl:copy-of select="@*"/>
            <xsl:apply-templates select="node()"></xsl:apply-templates>
        </xsl:copy>
    </xsl:template>
    
</xsl:stylesheet>

Open in new window


There is nothing in there you would not understand, but it might be a bit tricky.
It would please me if you at least looked at it from the learning perspective
0
 
Russell Scheinberg, MCSE Data Platform 2012IT Development AdministratorAuthor Commented:
Geert,

That was an awesome response. Works perfectly. Very complete answer and I really appreciate the tutorial so that I would not only get the correct format but understand it. I would like a little bit more clarification, if you don't mind, on the way that the four template sections interact with each other so that you get each record in the document to be written. Do they build on each other? Thanks.
0
 
Geert BormansCommented:
You are welcome

I don't mind adding some clarification on the last one

The apply-templates takes a selection of nodes and pushes them to the templates. Each node in the selection will choose the best matching template (most precise) and that template will execute on the node.

So, the process starts at the root of the document (the document node), with this template
<xsl:template match="/">
The context now is document node and we do
<xsl:apply-templates select="companies_report"/>
so we push the "companies_report" nodes (there is only one) to the templates

The most precise matching template is this one
<xsl:template match="companies_report">
so we will execute that on the node
The context now is the companies_report element
At this point we construct the securities element in the output tree
and
<xsl:apply-templates select="companies/company/security"/>
we push out all the security nodes (there are three of them)

Note that order of the templates is not important, the order of execution is triggered by the document hierarchy, not by the template order in the stylesheet

The template that has the best match for security is this one
    <xsl:template match="security">
So each security element will in turn be processed in that template

The security template has a
            <xsl:apply-templates select="description"/>
there is no obvious specific template for description, but there is a general one
    <xsl:template match="node()">
that will pick up the description node, since an element is a node()
it will reconstruct its tags, copy its attributes and wil
           <xsl:apply-templates select="node()"></xsl:apply-templates>
again for deeper nodes
(note that text nodes are also nodes, so it will hit this template again one level deeper,
text nodes have no tag nor attributes so nothing will happen with the text node, but copying the string value

I hope this makes sense

note: mccarl did the initial work in this question. His stylesheet was not much annotated, but did the job. You could have shown some appreciation for his work by splitting points and make his original reply an "assisted answer". Just a tip for a next question
0
 
Russell Scheinberg, MCSE Data Platform 2012IT Development AdministratorAuthor Commented:
You are right.
 I should have done that didn' t realize I could. My apologies t mccarl. I will see if I can fix that. Experts exchange is great because of ppl like both of you.
0
 
Russell Scheinberg, MCSE Data Platform 2012IT Development AdministratorAuthor Commented:
Yes. That would be great. I'd like to share the credit appropriately. Thanks.
0
 
Russell Scheinberg, MCSE Data Platform 2012IT Development AdministratorAuthor Commented:
A combination of great answers that helped give me a solution and understand how it's done. Thanks guys.
0
 
mccarlIT Business Systems Analyst / Software DeveloperCommented:
You're welcome!
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now