Need help with a transform

[Kashman]

Need a little help with XML/XSLT transform? What I am trying to achieve is to take the raw XML from an ADO recordset and then convert it to a hierarchical XML using ParentID as the child of the ID. If the ID is 0 then the element is at root level output like below:

<Row>
<Cell>Title</Cell>
<Cell>Description</Cell>
<Row>
<Cell>Title</Cell>
<Cell>Description</Cell>
<Row>
<Cell>Title</Cell>
<Cell>Description</Cell>
</Row>
</Row>
</Row>

With the little knowledge I have of XML and XSLT I came up with following transform but it is not working. I was wondering if you could provide some pointers on how to develop such a transform. If you are ever in the SFO area, the beer is on me .


=================== INPUT XML FILE ========================
<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
<s:Schema id="RowsetSchema">
<s:ElementType name="row" content="eltOnly">
<s:AttributeType name="ID" rs:number="1" rs:maydefer="true" rs:writeunknown="true">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="ParentID" rs:number="2" rs:nullable="true" rs:maydefer="true" rs:writeunknown="true">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="Title" rs:number="3" rs:nullable="true" rs:maydefer="true" rs:writeunknown="true">
<s:datatype dt:type="string" dt:maxLength="100"/>
</s:AttributeType>
<s:AttributeType name="Description" rs:number="4" rs:nullable="true" rs:maydefer="true" rs:writeunknown="true">
<s:datatype dt:type="string" dt:maxLength="100"/>
</s:AttributeType>
<s:AttributeType name="CloseForComments" rs:number="5" rs:nullable="true" rs:maydefer="true" rs:writeunknown="true">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="Url" rs:number="6" rs:nullable="true" rs:maydefer="true" rs:writeunknown="true">
<s:datatype dt:type="string" dt:maxLength="100"/>
</s:AttributeType>
<s:extends type="rs:rowbase"/>
</s:ElementType>
</s:Schema>
<rs:data>
<z:row ID="1" ParentID="0" Title="Security" Description="gfhfdgh" CloseForComments="1" Url="http://www.Yahoo.com"/>
<z:row ID="2" ParentID="1" Title="Specification" Description="dgfhdfgh" CloseForComments="1" Url="http://www.Yahoo.com"/>
<z:row ID="3" ParentID="2" Title="Version 1" Description="dfghgfh" CloseForComments="1" Url="http://www.Yahoo.com"/>
<z:row ID="4" ParentID="2" Title="Version 2" Description="gfdhdfgh" CloseForComments="1" Url="http://www.Yahoo.com"/>
<z:row ID="5" ParentID="1" Title="Hardware" Description="dfghdfgh" CloseForComments="1" Url="http://www.Yahoo.com"/>
<z:row ID="6" ParentID="5" Title="Silicon" Description="gfdhgdfh" CloseForComments="1" Url="http://www.Yahoo.com"/>
<z:row ID="7" ParentID="5" Title="Test Boards" Description="dgfhgh" CloseForComments="1" Url="http://www.Yahoo.com"/>
<z:row ID="8" ParentID="1" Title="Software" Description="dgfhgfdh" CloseForComments="1" Url="http://www.Yahoo.com"/>
<z:row ID="9" ParentID="8" Title="Security" Description="gfdhdfgh" CloseForComments="1" Url="http://www.Yahoo.com"/>
<z:row ID="10" ParentID="8" Title="Database" Description="dfghgh" CloseForComments="0" Url="http://www.Yahoo.com"/>
<z:row ID="11" ParentID="1" Title="Display" Description="gfdhdfghs" CloseForComments="0" Url="http://www.Yahoo.com"/>
<z:row ID="12" ParentID="0" Title="Communications" Description="sdfgsd" CloseForComments="0" Url="http://www.Yahoo.com"/>
<z:row ID="13" ParentID="12" Title="Specification" Description="dsfgfdg" CloseForComments="1" Url="http://www.Yahoo.com"/>
<z:row ID="14" ParentID="13" Title="Version 1" Description="sfdgsdfg" CloseForComments="1" Url="http://www.Yahoo.com"/>
<z:row ID="15" ParentID="13" Title="Version 2" Description="sdfgdsgf" CloseForComments="1" Url="http://www.Yahoo.com"/>
<z:row ID="16" ParentID="12" Title="Hardware" Description="sfdgfdg" CloseForComments="1" Url="http://www.Yahoo.com"/>
<z:row ID="17" ParentID="16" Title="Silicon" Description="fdsgfdg" CloseForComments="1" Url="http://www.Yahoo.com"/>
<z:row ID="18" ParentID="16" Title="Test Boards" Description="fdgfdgs" CloseForComments="1" Url="http://www.Yahoo.com"/>
<z:row ID="19" ParentID="12" Title="Software" Description="oiupoiu" CloseForComments="1" Url="http://www.Yahoo.com"/>
<z:row ID="20" ParentID="19" Title="Security" Description="uiop" CloseForComments="1" Url="http://www.Yahoo.com"/>
</rs:data>
</xml>=================== END OF XML FILE =======================


============== MY FAILED TRANSFORM ATTEMPT ==============
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset"
xmlns:z="#RowsetSchema" version="1.0">

<xsl:output omit-xml-declaration="yes" indent="yes"/>

<xsl:template match="/">
<xsl:apply-templates select="*/xml/rs:data/z:row[not(@ParentID = /*/xml/rs:data/z:row/@ID)]"/>
</xsl:template>

<xsl:template match="xml/rs:data/z:row">
<treenode Projects="{@Title}">
<xsl:apply-templates select="/*/xml/rs:data/z:row[@ParentID = current()/@ID]"/>
</treenode>
</xsl:template>
</xsl:stylesheet>
=================== END OF MY TRANSFORM ==================

[sorin_ristache]

Hello,

It is not clear what is the format of the transform output. I think you have better chances of receiving help if you post a smaller sample input XML document and the expected output XML document for that input.


Regards,
Sorin

[Kashman]

Consider this XML file:

<Table>
<Node ID="1" ParentID="0" Text="1"/>
<Node ID="2" ParentID="1" Text="1.1"/>
<Node ID="3" ParentID="2" Text="1.1.1"/>
<Node ID="4" ParentID="2" Text="1.1.2"/>
<Node ID="5" ParentID="2" Text="1.1.3"/>
<Node ID="6" ParentID="1" Text="1.2"/>
<Node ID="7" ParentID="6" Text="1.2.1"/>
</Table>

and this transform

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output omit-xml-declaration="yes" indent="yes"/>
<xsl:template match="/">
<xsl:apply-templates select="*/Node[not(@ParentID = /*/Node/@ID)]"/>
</xsl:template>
<xsl:template match="Node">
<treenode text="{@Text}">
<xsl:apply-templates select="/*/Node[@ParentID = current()/@ID]"/>
</treenode>
</xsl:template>
</xsl:stylesheet>


will produce the following nested result.
<?xml version="1.0" encoding="utf-8" ?>
<TREENODES>
<treenode text="1">
<treenode text="1.1">
<treenode text="1.1.1" />
<treenode text="1.1.2" />
<treenode text="1.1.3" />
</treenode>
<treenode text="1.2">
<treenode text="1.2.1"/>
</treenode>
</treenode>
</TREENODES>

I would like to get the same results but with the raw XML from an ADO record set posted aove.
The experiment I tried does not do the nesting, I think I am getting lost in the syntax somewhere also, it outputs the rs:data, z, schema information in the output which I don't want.

I hope it is clear now

[jkmyoung]

First, stop using /*/xml everywhere,-> this implies that you're using a xml node which is not the root (unless your input file is different than posted.)

Second, unless you have multiple z:row 's in your xml in other locations, you can simply use z:row as the template match string.

<xsl:template match="/">
<TREENODES>
<xsl:apply-templates select="/xml/rs:data/z:row[not(@ParentID = /xml/rs:data/z:row/@ID)]"/>
</TREENODES>
</xsl:template>

<xsl:template match="z:row">
<treenode Projects="{@Title}">
<xsl:apply-templates select="/xml/rs:data/z:row[@ParentID = current()/@ID]"/>
</treenode>
</xsl:template>

[Kashman]

Thanks it did the trick . Sometimes an extra pair of eyes help.

Best Regards,
Kash

[Kashman]

One more quick question. The root node has this schema info how do I get rid of that.


<TREENODES xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">

[sorin_ristache]

Add the exclude-result-prefixes="s dt rs z" attribute to the xsl:stylesheet element.


Regards,
Sorin