Adding nodes from 2 different xml files into a third xml

pallone · July 10th, 2012, 03:56 PM

Hi,

Please note that I am using XSLT 1.0 and ,NET 4

I have 3 xml files. One of them is the master and the other 2 are like look up tables. I have to bring elements from the look up files xml into the master one based on a matching element in the master xml.

In my master xml I have a node called <Firm> and I need to use the value of this node to look up the number of the firm in the second xml called lookup1.xml and add a new node in the master.xml with that value.

In my master I also have a node called <Type> which has a value and I need to look up the text representation in the lookup2.xml which is an attribute and bring that to the master.xml as a new node.

I canot figure out how I am going to match the nodes using the document function in a loop to able to look up the values for all the nodes in the collection.

This is the original Master.xml
<root>
<criteria>
<Type>A</Type>
<Firm>HBB</Firm>
<NameValue>Yellow</NameValue>
</criteria>
<criteria>
<Type>B</Type>
<Firm>CMM</Firm>
<NameValue>Blue</NameValue>
</criteria> </root>
Lookup1.xml
<Lookup1>
<Info>
<Core>Yes</Core>
<Firm>HBB</Firm>
<FirmCode>1111</FirmCode>
</Info>
<Info>
<Core>No</Core>
<Firm>CMM</Firm>
<FirmCode>222</FirmCode>
</Info>
</Lookup1>
Lookup2.xml
<Lookup2>
<Range>
<StartDate>05</StartDate>
<Firm type="Health">A</Firm>
<EndDate>31</EndDate>
</Range>
<Range>
<StartDate>10</StartDate>
<Firm type="House">B</Firm>
<EndDate>20</EndDate>
</Range>
</Lookup2>
The final Master xml should look like this:
<root>
<criteria>
<Type>A</Type>
<Firm>HBB</Firm>
<NameValue>Yellow</NameValue>
<FirmCode>1111</FirmCode>
<FirmTypeFromAttribute>Health</FirmTypeFromAttribute>
</criteria>
<criteria>
<Type>B</Type>
<Firm>CMM</Firm>
<NameValue>Blue</NameValue>
<FirmCode>222</FirmCode>
<FirmTypeFromAttribute>House</FirmTypeFromAttribute>
</criteria>
</root>

I would appreciate if someone with more experience could kindly show me how to match the nodes to produce the final xml as above
.
Cheers

C

pallone · July 10th, 2012, 06:19 PM

This is what I am trying but to no avail

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="/">
<xsl:variable name="xmlLookup1" select="document(Lookup1.xml
)" />
<xsl:variable name="xmlLookupl2" select="document(Lookup2.xml
)" />



For each criteria/Firm in Master.xml
If it matches <Firm>HBB</Firm> in Lookup1.xml
then create element in master.xml (<FirmCode>1111</FirmCode>)
<xsl:element name="{}">
<xsl:copy-of select="xmlLookup1/*/*" />
</xsl:element>
</xsl:template>

But I cannot figure out how to loop using the document function to match the nodes in different xml

Please any ideas?

Cheers

C

samjudson · July 11th, 2012, 06:48 AM

You can load the lookups into global variables, outside of any template. Then you can use them within any template. Also it is often easier if your variable names mean something (and you need single quotes around the filenames, as they are string literals).

Code:

<xsl:variable name="firmCodes" select="document('Lookup1.xml
')" /> 
<xsl:variable name="firmTypes" select="document('Lookup2.xml
')" />

Then I usually start with an identity template:

Code:

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

Then look at what you want to change. In this instance it is the <criteria> element. You want to process all its children, then add two new elements at the end based on the lookup values.

Code:

<xsl:template match="criteria">
  <xsl:copy>
    <xsl:apply-templates select="@*|node()"/>
    <xsl:variable name="firm" select="Firm"/>
    <xsl:variable name="type" select="Type"/>
    <FirmCode><xsl:value-of select="$firmCodes//Info[Firm=$firm]/FirmCode"/></FirmCode>
    <FirmTypeFromAttribute><xsl:value-of select="$firmTypes//Firm[@type=$type]"/></FirmTypeFromAttribute>
  </xsl:copy>
</xsl:template>

This could be speeded up with use of the <xsl:key> instruction, but assuming your lookup files aren't too large this should work fine.

pallone · July 11th, 2012, 08:17 AM

Hi Sam,

Thanks very very much for your help.

I was stuck with this issue.

I think that the look up files can grow quite a lot as well as the master one.

Could you please elaborate a bit more on how to use the keys to improve speed?

Also, I am assuming I will add the xml-stylesheet instruction at the top of the Master.xml. Am I right? And the xml files must be in the same folder???

<?xml-stylesheettype="text/xsl" href="../xsl/ANC601.xsl"?>

Cheers,

C

samjudson · July 11th, 2012, 09:30 AM

If you are using .Net then no, you probably wouldn't use the xml-stylesheet instruction as you'd be much better controlling the stylesheet etc from in code.

Using the XslCompiledTransform Class: http://msdn.microsoft.com/en-us/library/0610k0w4.aspx
Resolving External Resources During XSLT Processing: http://msdn.microsoft.com/en-us/library/be6kaf0h.aspx

As for the use of keys. You'd define two keys that reference the lookup elements like so:

Code:

<xsl:key name="codeKey" use="../Firm" match="Info/FirmCode"/>
<xsl:key name="typeKey" use="@type" match="Firm"/>

Then you call them using the key() xpath function, relative to the external document variables:

Code:

<FirmCode><xsl:value-of select="$firmCodes/key('codeKey', $firm)"/></FirmCode>
    <FirmTypeFromAttribute><xsl:value-of select="$firmTypes/key('typeKey', $type)"/></FirmTypeFromAttribute>

pallone · July 11th, 2012, 12:31 PM

Hi Sam,

thanks again for your help.

I had a look at the url you suggested and If I use this:

XslCompiledTransform xslt = newXslCompiledTransform();
xslt.Load(Server.MapPath("XSL/Master.xslt"));
xslt.Transform(Server.MapPath("XML/Master.xml"), Server.MapPath("~/ANC601.xml"));

1 - I assume the other xml files referenced with the document function will have to be in the same folder as the master.xml, right?

2 - Is it possible to pass some parameters to the xslt using the XslCompiledTransform class? I will need to pass a couple of parameters.

Cheers,

C

Martin Honnen · July 11th, 2012, 12:35 PM

Use an XsltArgumentList: http://msdn.microsoft.com/en-us/library/dfktf882

pallone · July 11th, 2012, 02:20 PM

Hi Martin,

Thanks for your reply. I will try it tomorrow.

Cheers

C