Help with Querying XML Document

[VJ]

Hello All,

The xml document below describes the contents of a folder in a
Document Managment System. I need to retrieve the DocId for the most
recently added (<Add_Date>) spreadsheet file(<name> ends with ".XLS").
As a newbie to XML, XSL and XPATH, I wanted to bounce some ideas off
the more experienced folks in this group.

<?xml version="1.0" encoding="utf-8" ?>
<FolderDetails xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://tempuri.org/">
<Docs>
<DocumentDetails>
<Name>EIU.xls</Name>
<Version>1</Version>
<Add_Date>10/8/2003 3:41:51 PM</Add_Date>
<DocType>E</DocType>
<DocId>15607779</DocId>
</DocumentDetails>
<DocumentDetails>
<Name>MAKE.xls</Name>
<Version>1</Version>
<Add_Date>10/17/2003 8:22:43 AM</Add_Date>
<DocType>E</DocType>
<DocId>15608338</DocId>
</DocumentDetails>
</Docs>
</FolderDetails>


My first thought was to transform this XML into another XML which is
sorted on Add_date (descending order) and then pick the very first
occurance of docId. But there does not seem to be a direct way to
sort on a date value.

I know this is an awfully inadequate explanation of what I am
attempting to do. But I am asking for any ideas or sample code to
quickly and efficiently accomplish this. I am under a tremendous
amount of pressure so any help, ideas, code snippets.. gratefully
accepted...

Thanks in advance,
Jay

 

[Dimitre Novatchev]

The xml document below describes the contents of a folder in a

Document Managment System. I need to retrieve the DocId for the most
recently added (<Add_Date>) spreadsheet file(<name> ends with ".XLS").

This transformation:

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

<xslutput omit-xml-declaration="yes"/>
<xsl:template match="Docs">
<xsl:for-each
select="DocumentDetails[substring(Name,
string-length(Name) - 3
)
=
'.xls'
]">
<xsl:sort
select="substring-before(substring-after(substring-after(Add_Date, '/'),
'/'), ' ')"
data-type="number"/>

<xsl:sort select="substring-before(Add_Date, '/')"
data-type="number"/>

<xsl:sort select="substring-before(substring-after(Add_Date, '/'),
'/')"
data-type="number"/>

<xsl:sort select="substring(Add_Date, string-length(Add_Date) - 1)"
/>

<xsl:sort
select="translate(substring-before(substring-after(Add_Date, ' '), ' '),
':', '')"
data-type="number"/>

<xsl:if test="position() = last()">
<xsl:copy-of select="."/>
</xsl:if>
</xsl:for-each>
</xsl:template>

</xsl:stylesheet>

when applied on this source.xml:

<FolderDetails>
<Docs>
<DocumentDetails>
<Name>EIU.xls</Name>
<Version>1</Version>
<Add_Date>10/8/2003 3:41:51 PM</Add_Date>
<DocType>E</DocType>
<DocId>15607779</DocId>
</DocumentDetails>
<DocumentDetails>
<Name>MAKE.xls</Name>
<Version>1</Version>
<Add_Date>10/18/2001 8:22:43 PM</Add_Date>
<DocType>E</DocType>
<DocId>15608338</DocId>
</DocumentDetails>
<DocumentDetails>
<Name>MAKE.xls</Name>
<Version>1</Version>
<Add_Date>10/18/2001 8:22:44 AM</Add_Date>
<DocType>E</DocType>
<DocId>15608338</DocId>
</DocumentDetails>
<DocumentDetails>
<Name>MAKE.xls</Name>
<Version>1</Version>
<Add_Date>10/18/2001 8:22:43 AM</Add_Date>
<DocType>E</DocType>
<DocId>15608338</DocId>
</DocumentDetails>
<DocumentDetails>
<Name>MAKE.xls</Name>
<Version>1</Version>
<Add_Date>10/17/2001 8:22:43 AM</Add_Date>
<DocType>E</DocType>
<DocId>15608338</DocId>
</DocumentDetails>
<DocumentDetails>
<Name>MAKE.xls</Name>
<Version>1</Version>
<Add_Date>9/17/2001 8:22:43 AM</Add_Date>
<DocType>E</DocType>
<DocId>15608338</DocId>
</DocumentDetails>
</Docs>
</FolderDetails>

will correctly sort it by Add_Date (sorting correctly by year, month, date,
AM/PM and time) and produce the DocumentDetails element with the latest
value of Add_Date. In this particular case the result is:

<DocumentDetails>
<Name>EIU.xls</Name>
<Version>1</Version>
<Add_Date>10/8/2003 3:41:51 PM</Add_Date>
<DocType>E</DocType>
<DocId>15607779</DocId>
</DocumentDetails>

If one does not want to write such code, they may use the str-split-to-words
template from FXSL.


=====
Cheers,

Dimitre Novatchev.
http://fxsl.sourceforge.net/ -- the home of FXSL

 

[VJ]

Hi Dimitre,

Thank you for your response. It works like a charm IF I don't have the
namespace declaration as part of the root node <FolderDetails>.
However since the xml I receive already has the namespace declaration
in it, I can not seem to get this to produce the desired result.


if this :
<FolderDetails xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://tempuri.org/">

looks like this:

<FolderDetails>

Then your xsl is the answer. But since it isn't any ideas on what
should be tweaked?..


Thank you,
Jay

The xml document below describes the contents of a folder in a
Document Managment System. I need to retrieve the DocId for the most
recently added (<Add_Date>) spreadsheet file(<name> ends with ".XLS").

This transformation:

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

<xslutput omit-xml-declaration="yes"/>
<xsl:template match="Docs">
<xsl:for-each
select="DocumentDetails[substring(Name,
string-length(Name) - 3
)
=
'.xls'
]">
<xsl:sort
select="substring-before(substring-after(substring-after(Add_Date, '/'),
'/'), ' ')"
data-type="number"/>

<xsl:sort select="substring-before(Add_Date, '/')"
data-type="number"/>

<xsl:sort select="substring-before(substring-after(Add_Date, '/'),
'/')"
data-type="number"/>

<xsl:sort select="substring(Add_Date, string-length(Add_Date) - 1)"
/>

<xsl:sort
select="translate(substring-before(substring-after(Add_Date, ' '), ' '),
':', '')"
data-type="number"/>

<xsl:if test="position() = last()">
<xsl:copy-of select="."/>
</xsl:if>
</xsl:for-each>
</xsl:template>

</xsl:stylesheet>

when applied on this source.xml:

<FolderDetails>
<Docs>
<DocumentDetails>
<Name>EIU.xls</Name>
<Version>1</Version>
<Add_Date>10/8/2003 3:41:51 PM</Add_Date>
<DocType>E</DocType>
<DocId>15607779</DocId>
</DocumentDetails>
<DocumentDetails>
<Name>MAKE.xls</Name>
<Version>1</Version>
<Add_Date>10/18/2001 8:22:43 PM</Add_Date>
<DocType>E</DocType>
<DocId>15608338</DocId>
</DocumentDetails>
<DocumentDetails>
<Name>MAKE.xls</Name>
<Version>1</Version>
<Add_Date>10/18/2001 8:22:44 AM</Add_Date>
<DocType>E</DocType>
<DocId>15608338</DocId>
</DocumentDetails>
<DocumentDetails>
<Name>MAKE.xls</Name>
<Version>1</Version>
<Add_Date>10/18/2001 8:22:43 AM</Add_Date>
<DocType>E</DocType>
<DocId>15608338</DocId>
</DocumentDetails>
<DocumentDetails>
<Name>MAKE.xls</Name>
<Version>1</Version>
<Add_Date>10/17/2001 8:22:43 AM</Add_Date>
<DocType>E</DocType>
<DocId>15608338</DocId>
</DocumentDetails>
<DocumentDetails>
<Name>MAKE.xls</Name>
<Version>1</Version>
<Add_Date>9/17/2001 8:22:43 AM</Add_Date>
<DocType>E</DocType>
<DocId>15608338</DocId>
</DocumentDetails>
</Docs>
</FolderDetails>

will correctly sort it by Add_Date (sorting correctly by year, month, date,
AM/PM and time) and produce the DocumentDetails element with the latest
value of Add_Date. In this particular case the result is:

<DocumentDetails>
<Name>EIU.xls</Name>
<Version>1</Version>
<Add_Date>10/8/2003 3:41:51 PM</Add_Date>
<DocType>E</DocType>
<DocId>15607779</DocId>
</DocumentDetails>

If one does not want to write such code, they may use the str-split-to-words
template from FXSL.


=====
Cheers,

Dimitre Novatchev.
http://fxsl.sourceforge.net/ -- the home of FXSL

Hello All,

The xml document below describes the contents of a folder in a
Document Managment System. I need to retrieve the DocId for the most
recently added (<Add_Date>) spreadsheet file(<name> ends with ".XLS").
As a newbie to XML, XSL and XPATH, I wanted to bounce some ideas off
the more experienced folks in this group.

<?xml version="1.0" encoding="utf-8" ?>
<FolderDetails xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://tempuri.org/">
<Docs>
<DocumentDetails>
<Name>EIU.xls</Name>
<Version>1</Version>
<Add_Date>10/8/2003 3:41:51 PM</Add_Date>
<DocType>E</DocType>
<DocId>15607779</DocId>
</DocumentDetails>
<DocumentDetails>
<Name>MAKE.xls</Name>
<Version>1</Version>
<Add_Date>10/17/2003 8:22:43 AM</Add_Date>
<DocType>E</DocType>
<DocId>15608338</DocId>
</DocumentDetails>
</Docs>
</FolderDetails>


My first thought was to transform this XML into another XML which is
sorted on Add_date (descending order) and then pick the very first
occurance of docId. But there does not seem to be a direct way to
sort on a date value.

I know this is an awfully inadequate explanation of what I am
attempting to do. But I am asking for any ideas or sample code to
quickly and efficiently accomplish this. I am under a tremendous
amount of pressure so any help, ideas, code snippets.. gratefully
accepted...

Thanks in advance,
Jay

 

[VJ]

Dimitre,

Thanks for your response. Your xsl works perfectly IF the
<FolderDetails> root node does not contain namespace declarations.

If the root node looks like this
<FolderDetails>

It works great. However, my xml root looks like this:

<FolderDetails xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://tempuri.org/">

and so when I do the transform, it lists everything in the xml
document and does not produce the desired result...

Any ideas?.


Thanks again for your reply.
Jay

 

[Dimitre Novatchev]

This is a VFAQ.

Read about the problem and the solution here:

"None of my XPath select statements will work going against an XML file with
a default namespace. Help! "

at:
http://www.topxml.com/people/bosley/defaultns.asp



=====
Cheers,

Dimitre Novatchev.
http://fxsl.sourceforge.net/ -- the home of FXSL