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