Convert XML into rowset schema. 1

[mojotexas]

Hi, I am using InfoPath to fill out forms. The XML output looks nice, but I need to import it into a database application (iBase) that only wants XML in MS Rowset schema.

How can I transform my XML files to MS Rowset schema?

this is an example of my XML output from InfoPath.

<?xml version="1.0" encoding="UTF-8"?><?mso-infoPathSolution solutionVersion="1.0.0.147" productVersion="11.0.8034" PIVersion="1.0.0.0" href="file:///C:\Documents%20and%20Settings\hendrick\Desktop\VWUrequest.xsn" name="urn:schemas-microsoft-com:office:infopath:VWUrequest:-myXSD-2007-06-14T09-11-42" ?><?mso-application progid="InfoPath.Document"?><?mso-infoPath-file-attachment-present?><my:myFields xmlns:xsi="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema-instance"[/URL] xmlns:xhtml="[URL unfurl="true"]http://www.w3.org/1999/xhtml"[/URL] xmlns:w="[URL unfurl="true"]http://schemas.microsoft.com/office/word/2003/wordml"[/URL] xmlns:v="urn:schemas-microsoft-com:vml" xmlns:w10="urn:schemas-microsoft-com:office:word" xmlns:sl="[URL unfurl="true"]http://schemas.microsoft.com/schemaLibrary/2003/core"[/URL] xmlns:aml="[URL unfurl="true"]http://schemas.microsoft.com/aml/2001/core"[/URL] xmlns:wx="[URL unfurl="true"]http://schemas.microsoft.com/office/word/2003/auxHint"[/URL] xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:wsp="[URL unfurl="true"]http://schemas.microsoft.com/office/word/2003/wordml/sp2"[/URL] xmlns:st1="urn:schemas-microsoft-com:office:smarttags" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="[URL unfurl="true"]http://www.w3.org/TR/REC-html40"[/URL] xmlns:my="[URL unfurl="true"]http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-06-14T09:11:42"[/URL] xmlns:xd="[URL unfurl="true"]http://schemas.microsoft.com/office/infopath/2003"[/URL] xml:lang="en-gb">
	<my:wifDateSubmitted>2008-03-03</my:wifDateSubmitted>
	<my:wif1Party>2</my:wif1Party>
	<my:wif1OtherParty></my:wif1OtherParty>
	<my:wif1Requestor>Joe Schmoe</my:wif1Requestor>
	<my:wif1Title>Boss</my:wif1Title>
	<my:wif1Team>X-men</my:wif1Team>

thanks

 

[tsuji]

How would those data (my:wifDateSunitted etc) be allocated as row and column? All in a column?

 

[mojotexas]

Tsuji, thanks for your reply. In answer:

each <my:wif...></...> is a seperate column. The data between the tags then all belongs to a single record. There is a seperate XML document for each record.

So, If I were representing this as a CSV, the data structure would look like this:

wifDateSubmitted, wif1Party, wif1OtherParty, wif1Requestor, wif1Title, wif1Team
2008-03-03, 2, , Joe Schmoe, Boss, X-men

(this is abbreviated, there are about 75 columns)

 

[tsuji]

This initiating xsl document may get you a good (I hope) start. It is restricted to the Worksheet/Table part. Data types are all taken as String. You have also to expand additional namespace for other parts such as DocumentSettings, Styles, WorksheetOptions etc. In any case, it concentrates on the data part.
[tt]
<?xml version="1.0" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="[ignore]

http://www.w3.org/1999/XSL/Transform

[/ignore]"
xmlns="urn:schema-microsoft-comfficeffice"
xmlns:x="urn:schema-microsoft-com:excel"
xmlns:ss="urn:schema-microsoft-comffice:spreadsheet"
xmlns:my="[ignore]

http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-06-14T09:11:42

[/ignore]"
xmlns="urn:schema-microsoft-comffice:spreadsheet"
exclude-result-prefixes="my"
>
<xslutput method="xml" encoding="utf-8" indent="yes" />
<xsl:variable name="countRows" select="count(//my:wifDateSubmitted)" />
<xsl:variable name="countColumns" select="count(my:myFields//*) div $countRows" />
<xsl:template match="/">
<xslrocessing-instruction name="mso-microsoft">progid=Excel.Sheet"</xslrocessing-instruction>
<Workbook>
<xsl:attribute name="ss:Name">Sheet1</xsl:attribute>
<Table>
<xsl:attribute name="ss:ExpandedColumnCount"><xsl:value-of select="$countColumns" /></xsl:attribute>
<xsl:attribute name="ss:ExpandedRowCount"><xsl:value-of select="$countRows" /></xsl:attribute>
<xsl:attribute name="x:FullColumns">1</xsl:attribute>
<xsl:attribute name="x:FullRows">1</xsl:attribute>
<xsl:apply-templates select="my:myFields" />
</Table>
</Workbook>
</xsl:template>
<xsl:template match="my:myFields">
<xsl:apply-templates select="my:wifDateSubmitted" />
</xsl:template>
<xsl:template match="my:myFields/my:wifDateSubmitted">
<Row>
<Cell><Data ss:Type="String"><xsl:value-of select="normalize-space()" /></Data></Cell>
<xsl:if test="following-sibling::*[1][name() != 'my:wifDateSubmitted']">
<xsl:apply-templates select="following-sibling::*[1]" />
</xsl:if>
</Row>
</xsl:template>
<xsl:template match="my:myFields/*[name() != 'my:wifDateSubmitted']">
<Cell><Data ss:Type="String"><xsl:value-of select="normalize-space()" /></Data></Cell>
<xsl:if test="following-sibling::*[1][name() != 'my:wifDateSubmitted']">
<xsl:apply-templates select="following-sibling::*[1]" />
</xsl:if>
</xsl:template>
</xsl:stylesheet>
[/tt]

 

[tsuji]

Amendment
I seem to have missed out an open quote here. But it is not a runtime type of exception for the transformation.

[tt]<xslrocessing-instruction name="mso-microsoft">progid=[red]"[/red]Excel.Sheet"</xslrocessing-instruction>[/tt]

 

[mojotexas]

Does this amendment come right after the declaration? (or where does it fit?)

thanks for the fantastic help.

mojo

 

[mojotexas]

I'm sorry, I got it, forget the last question...

 

[tsuji]

Amendment-amended
Sorry, actually the amendment should correct another mistake. It should read like this.

[tt]<xslrocessing-instruction name="mso-[red]application[/red]">progid=[red]"[/red]Excel.Sheet"</xslrocessing-instruction>[/tt]