XML noobie help

[Slinger1010]

Hello

I'm attempting to convert our database from one format to another and I need to get it itno excel or access in order to do part of the conversion, I'm having a bit of a problem with the xmlns section, since I really know nothing about this I'm at a loss.

I think that one of the links is no longer valid, I don't understand actually why there are any links to the web at all, I have the XML file and an XSD file shouldn't that be all that is needed?

<gmdata xmlns:xsi="

http://www.w3.org/2001/XMLSchema-instance"

xmlns="

http://www.frontrange.com/goldmine/xmlexport"

xsi:namespaceSchemaLocation="

http://www.frontrange.com/goldmine/xmlexport&#32;gmschema.xsd"

>

I can supply more info on request (if I can) but I really need to know how to make this thing work (load into Excel)
Can anyone help??

Ron

 

[k5tm]

Do you have the gm namespace alias defined in your xsl:stylesheet element? (If the stylesheet is small enough, show the whole thing please.)

Tom Morrison

http://www.liant.com

 

[Slinger1010]

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="[URL unfurl="true"]http://www.w3.org/1999/XSL/Transform"[/URL] xmlns:xsi="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema-instance"[/URL] xmlns="[URL unfurl="true"]http://www.frontrange.com/goldmine/xmlexport"[/URL] xmlns:gm="[URL unfurl="true"]http://www.frontrange.com/goldmine/xmlexport">[/URL]
	<xsl:output method="text"/>
	<xsl:variable name="newline">
		<xsl:text></xsl:text>
	</xsl:variable>
	<xsl:template match="/">
		<xsl:for-each select="gm:gmdata/gm:header/gm:gmdbdef/gm:gmdbfld[@flddbfname='CAL']">
			<xsl:value-of select="concat('&quot;',@fldfname,'&quot;,')"/>
		</xsl:for-each>
		<xsl:value-of select="$newline"/>
		<xsl:for-each select="gm:gmdata/gm:accounts/gm:account">
			<xsl:value-of select="concat('&quot;',@gm_recid,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',@gm_accountno,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='ACCOUNTNO']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='ADDRESS1']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='ADDRESS2']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='ADDRESS3']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='CITY']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='COMPANY']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='CONTACT']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='COUNTRY']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='CREATEAT']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='CREATEBY']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='CREATEON']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="$newline"/>
		</xsl:for-each>/!</xsl:template>
</xsl:stylesheet>

Ron

 

[k5tm]

            <xsl:value-of select="concat('&quot;',[red]@fldname[/red],'&quot;,')"/>

Tom Morrison

http://www.liant.com

 

[k5tm]

...and to get rid of the trailing comma...

        <xsl:for-each select="gm:gmdata/gm:header/gm:gmdbdef/gm:gmdbfld[@flddbfname='CAL']">
            <xsl:value-of select="concat('&quot;',@fldname,'&quot;')"/>
			<xsl:if test="position()!=last()"><xsl:text>,</xsl:text></xsl:if>
        </xsl:for-each>

Tom Morrison

http://www.liant.com

 

[Slinger1010]

Thanks for your help, I managed to get one whole DB into a CSV, the other DB's should only need the fieldnames changed.

The only thing I needed to change was:

<xsl:variable name="newline">
        <xsl:text></xsl:text>
</xsl:variable>

to this

<xsl:variable name="newline">
        <xsl:text>&#10;</xsl:text>
</xsl:variable>

Ron

 

[k5tm]

Ron,

You may not have noticed, or may have lost in the copy/paste, a subtle difference in my coding of the newline variable.

<xsl:variable name="newline"><xsl:text>
</xsl:text></xsl:variable>

I put the newline (or whatever manages to create a new line on the system) inside the xsl:text. Obviates the need for a numeric entity.

I am glad it is working for you. Easier than wrestling Excel into submission?

Tom Morrison

http://www.liant.com

 

[k5tm]

Ron,

Also, since Sugar CRM is open source, you are encouraged to contribute your work back to the community. Here is the web site that tells you how to contribute. It seems that most of the complaints about Goldmine->Sugar have to do with the nastiness of the Goldmine database, which you are unraveling.

Tom Morrison

http://www.liant.com

 

[Slinger1010]

Once I have created an XSLT for each DB I will post it there.


As for the new line, The CSV came out as one long line. I will post my new XSLT, can you point out to me where the error would be in the newline variable?

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="[URL unfurl="true"]http://www.w3.org/1999/XSL/Transform"[/URL] xmlns:xsi="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema-instance"[/URL] xmlns="[URL unfurl="true"]http://www.frontrange.com/goldmine/xmlexport"[/URL] xmlns:gm="[URL unfurl="true"]http://www.frontrange.com/goldmine/xmlexport">[/URL]
	<xsl:output method="text"/>
	<xsl:variable name="newline">
		<xsl:text>&#10;</xsl:text>
	</xsl:variable>
	<xsl:template match="/">
		<xsl:for-each select="gm:gmdata/gm:header/gm:gmdbdef/gm:gmdbfld[@flddbfname='CONTACT1']">
			<xsl:value-of select="concat('&quot;',@fldname,'&quot;')"/>
			<xsl:if test="position()!=last()">
				<xsl:text>,</xsl:text>
			</xsl:if>
		</xsl:for-each>
		<xsl:value-of select="$newline"/>
		<xsl:for-each select="gm:gmdata/gm:accounts/gm:account">
			<!--<xsl:value-of select="concat('&quot;',@gm_recid,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',@gm_accountno,'&quot;,')"/>-->
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='ACCOUNTNO']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='ADDRESS1']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='ADDRESS2']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='ADDRESS3']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='CITY']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='COMPANY']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='CONTACT']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='COUNTRY']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='CREATEAT']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='CREATEBY']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='CREATEON']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='DEAR']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='DEPARTMENT']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='EXT1']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='EXT2']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='EXT3']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='EXT4']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='FAX']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='KEY1']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='KEY2']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='KEY3']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='KEY4']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='KEY5']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='LASTDATE']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='LASTNAME']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='LASTTIME']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='LASTUSER']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='MERGECODES']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='NOTES']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='OWNER']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='PHONE1']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='PHONE2']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='PHONE3']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='SECR']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='SOURCE']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='STATE']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='STATUS']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='TITLE']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:properties/gm:property[@db_name='ZIP']/gm:property_string,'&quot;,')"/>
			<xsl:value-of select="$newline"/>
		</xsl:for-each>/!</xsl:template>
</xsl:stylesheet><!-- Stylus Studio meta-information - (c) 2004-2006. Progress Software Corporation. All rights reserved.
<metaInformation>
<scenarios ><scenario default="yes" name="Scenario1" userelativepaths="yes" externalpreview="no" url="Copy of Cal  contacts.xml" htmlbaseurl="" outputurl="" processortype="internal" useresolver="yes" profilemode="0" profiledepth="" profilelength="" urlprofilexml="" commandline="" additionalpath="" additionalclasspath="" postprocessortype="none" postprocesscommandline="" postprocessadditionalpath="" postprocessgeneratedext="" validateoutput="no" validator="internal" customvalidator=""/></scenarios><MapperMetaTag><MapperInfo srcSchemaPathIsRelative="yes" srcSchemaInterpretAsXML="no" destSchemaPath="" destSchemaRoot="" destSchemaPathIsRelative="yes" destSchemaInterpretAsXML="no" ><SourceSchema srcSchemaPath="Copy of Cal  contacts.xml" srcSchemaRoot="gmdata" AssociatedInstance="" loaderFunction="document" loaderFunctionUsesURI="no"/></MapperInfo><MapperBlockPosition><template match="/"><block path="xsl:for&#x2D;each" x="271" y="0"/><block path="xsl:for&#x2D;each/xsl:value&#x2D;of" x="249" y="0"/><block path="xsl:value&#x2D;of" x="209" y="0"/><block path="xsl:for&#x2D;each[1]" x="298" y="0"/><block path="xsl:for&#x2D;each[1]/xsl:value&#x2D;of" x="248" y="0"/><block path="xsl:for&#x2D;each[1]/xsl:value&#x2D;of[1]" x="208" y="0"/><block path="xsl:for&#x2D;each[1]/xsl:value&#x2D;of[2]" x="168" y="120"/><block path="xsl:for&#x2D;each[1]/xsl:value&#x2D;of[3]" x="289" y="40"/><block path="xsl:for&#x2D;each[1]/xsl:value&#x2D;of[4]" x="209" y="40"/><block path="xsl:for&#x2D;each[1]/xsl:value&#x2D;of[5]" x="169" y="40"/><block path="xsl:for&#x2D;each[1]/xsl:value&#x2D;of[6]" x="129" y="40"/><block path="xsl:for&#x2D;each[1]/xsl:value&#x2D;of[7]" x="89" y="40"/><block path="xsl:for&#x2D;each[1]/xsl:value&#x2D;of[8]" x="49" y="40"/><block path="xsl:for&#x2D;each[1]/xsl:value&#x2D;of[9]" x="249" y="80"/><block path="xsl:for&#x2D;each[1]/xsl:value&#x2D;of[10]" x="289" y="80"/><block path="xsl:for&#x2D;each[1]/xsl:value&#x2D;of[11]" x="209" y="80"/><block path="xsl:for&#x2D;each[1]/xsl:value&#x2D;of[12]" x="169" y="80"/><block path="xsl:for&#x2D;each[1]/xsl:value&#x2D;of[13]" x="129" y="80"/><block path="" x="89" y="80"/></template></MapperBlockPosition><TemplateContext></TemplateContext><MapperFilter side="source"></MapperFilter></MapperMetaTag>
</metaInformation>
-->

Thanks
Ron

 

[k5tm]

Ron,

What you are doing with the newline variable is fine. I was just pointing out my technique for 'capturing' a new line in a variable, which doesn't require remembering &#10;.

Tom Morrison

http://www.liant.com