XML noobie help

Slinger1010 · Apr 5, 2007

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 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 · Apr 5, 2007

Ron,

Those xmlns things are called namespace, and those are not links but rather URIs. As such, they are not actual URLs (locations on the web) but simply unique identifiers. Some tutoial information may be found here.

What is the source database, Goldmine? What is the destination database? Is the XSD for the source or the destination? What particular part of the conversion are you doing in Excel/Access? Perhaps that step can be done in a different, simpler manner.

Tom Morrison

http://www.liant.com

Slinger1010 · Apr 5, 2007

Yes we are converting Goldmine to Sugar, the XSD is the source from goldmine. I was planning on converting the XML to CSV to allow the import into Sugar.

I'm all up for a simpler manner!! I'm sure there is an easier way but since I have next to no experience in this, I have no idea.

Ron

k5tm · Apr 5, 2007

One of the nice things about XSLT is its ability to convert XML documents, targeting XML, HTML or text (which is what would be used for CSV).

Given that you have the XSD for the source, you can use a tool such as Stylus Studio or Altova XMLSpy to create an XSLT (XML translating stylesheet) that will output a CSV file. Both of these products are available on a free-trial basis, so it would cost you nothing to try this approach.

If you don't know about XSLT, there is a tutorial here.

I had a look around the various Sugar websites, and it seem that importing information is one major weakness of the Sugar CRM. Since it is an open source community, if you create a successful XSLT stylesheet, it would be a 'nice thing' to contribute it to the open source community. There didn't seem to be any clear, follow-this-and-you-will-succeed, style instructions on the Sugar forums or documentation. In fact, the documentation seems to ignore the obvious need for migration.

(I did this same thing -- importing from many disparate databases -- when our company converted to Salesforce. This looks like one place where Salesforce is quite superior to Sugar.)

Tom Morrison

http://www.liant.com

tsuji · Apr 7, 2007

>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?
At what step of your processing do you encounter problem and come to the conclusion that "one of the links is no longer valid"?

Your gmdata tag is definitely incorrect if you validate the document and _not_ overriding the schema location specification. It does not necessarily mean things automatically stop functioning. If no validation is performed along the way or that schema location is overridden, those "broken" links are not necessrily going to stop the functioning of the process. You need to specify at what step... (the question above).

But the most disturbing is this.
xsi:NamespaceSchemaLocation [red]never heard of it in w3c xml schema language[/red]
What is it? where do you get it from, which standard?

Slinger1010 · Apr 9, 2007

We were trying to import the XML file into Excel (Data --> XML --> Import) and the error we got involved the URL's themselves actualy the error involves this same line you pointed out. "namespaceSchemaLocation" attribute not defined.

This XML file was created by Goldmine.

Ron

tsuji · Apr 9, 2007

[1] If the error points particularly to that bit, try replace it by this.
[tt]
<gmdata xmlns:xsi="

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

xmlns="

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

[red]xsi:schemaLocation="

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

[highlight] [/highlight][/red]

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

>
[/tt]
And if that schema file (you said you get it) is stored in the local file system, then change the last "

http://....xsd"

to point to your local copy. If it is on the same directory as the application, just put down the file name there instead.

[2] Then look at the xsd file, make sure it has a targetNamespace pointing to the same default namespace.

Slinger1010 · Apr 9, 2007

No such luck now I get the same error with this "xsi:schemaLocation", when I remove the line entirely it stops at another line and says that you can only have one top level entry, I may be going about this the wrong way entirely maybe Excel isn't the best tool for this job.

Ron

k5tm · Apr 9, 2007

Ron said:
maybe Excel isn't the best tool for this job.

Ron, I think this is a correct assessment.

Do you really have an XML Schema (a.k.a. an XSD) file? If so, you really can create an XSLT stylesheet that will produce a CSV file directly without any other steps. Are you ready to give it a try?

Tom Morrison

http://www.liant.com

Slinger1010 · Apr 9, 2007

Yes I do have an XSD file and I'm willing to give it a try. I did manage to load the XML file into Stylus Studio and I ca see the entire file parsed out properly in the tree view, but I have no idea how to get this into a CSV file from that point.

Unless you have another idea? I'm open to any options.

Ron

k5tm · Apr 9, 2007

Okay, I don't have actual Goldmine data, but you can get the idea from this.

Copy out this sample data and, using Stylus Studio, put it in a New -> XML Document:

Code:

<?xml version="1.0"?>
<gmdata xmlns:xsi="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema-instance"[/URL] 
        xmlns="[URL unfurl="true"]http://www.frontrange.com/goldmine/xmlexport"[/URL] 
		xsi:namespaceSchemaLocation="[URL unfurl="true"]http://www.frontrange.com/goldmine/xmlexport&amp;#32;gmschema.xsd"[/URL] >
<contact>
	<name>
		<firstname>Bill</firstname>
		<lastname>Smith</lastname>
	</name>
	<phone>
		<business>800-555-1212</business>
	</phone>
</contact>
<contact>
	<name>
		<firstname>Bubba</firstname>
		<lastname>Jones</lastname>
	</name>
	<phone>
		<business>800-555-2323</business>
		<cell>888-555-1234</cell>
	</phone>
</contact>

</gmdata>

Open a New-> XSLT Stylesheet and copy the following to it.

Code:

<?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:contact">
	<xsl:value-of select="concat('&quot;',gm:name/gm:lastname,'&quot;,')"/>
	<xsl:value-of select="concat('&quot;',gm:name/gm:firstname,'&quot;,')"/>
	<xsl:value-of select="concat('&quot;',gm:phone/gm:business,'&quot;,')"/>
	<xsl:value-of select="concat('&quot;',gm:phone/gm:cell,'&quot;')"/>
	<xsl:value-of select="$newline"/>
</xsl:for-each>

</xsl:template>

Press the green arrow and you should get this in the output window (you will have to select the preview text mode in the output window).

Code:

"Smith","Bill","800-555-1212",""
"Jones","Bubba","800-555-2323","888-555-1234"

Now I know the actual Goldmine input document is considerably more complex, but that is handled by the XPath expressions in the xsl:value-of selections. So, changing the XPath expressions to pull the correct data would seem fairly easy. Give it a try.

Since you are creating a CSV file the XSLT Mapper tab does not show anything that is very usable. The XSLT Mapper is very nice for XML-to-XML translations.

Tom Morrison

http://www.liant.com

k5tm · Apr 9, 2007

Ron,

Save the sample input document before creating the new XSLT.

Then...

When you create the new XSLT, you will be presented with a Scenario dialog box. Press the ellipsis button next to the SourceXML URL box to select the sample input document.

Tom Morrison

http://www.liant.com

Slinger1010 · Apr 10, 2007

Thanks, I got it to work.

I needed to put "</xsl:stylesheet>" at the end though.

I'll see if I can use it on my partial xml.

Ron

k5tm · Apr 10, 2007

Ron,

Sorry about missing the closing element. Copy Paste Dysfunction Syndrome. [blush]

Tom Morrison

http://www.liant.com

Slinger1010 · Apr 10, 2007

My first attempt was a bust, I managed to get empty quotes.
Here is what I attempted:

<?xml version='1.0'?>
<xsl:stylesheet version="1.0" xmlns:xsl="
http://www.w3.org/1999/XSL/Transform"
xmlns:xsi="
http://www.w3.org/2001/XMLSchema-instance"
xmlns="
http://www.frontrange.com/goldmine/xmlexport"
xmlns:gm="
http://www.frontrange.com/goldmine/xmlexport"
>
<xslutput method="text"/>
<xsl:variable name="newline"><xsl:text>
</xsl:text></xsl:variable>

<xsl:template match="/">
<xsl:for-each select="/gm:gmdata/gm:accounts">
<xsl:value-of select="concat('"',gm:account/gm:gm_recid,'",')"/>
<xsl:value-of select="concat('"',gm:account/gm:gm_accountno,'",')"/>
<xsl:value-of select="concat('"',gm:account/gmroperties/gm:business,'",')"/>
<xsl:value-of select="concat('"',gm:account/gmroperties/gm:name,'"')"/>
<xsl:value-of select="$newline"/>
</xsl:for-each>

</xsl:template>
</xsl:stylesheet>

And I'll post one partial record if that will help in explaining it to me. I really appreciate your help and patience with me.

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<gmdata xmlns:xsi="
http://www.w3.org/2001/XMLSchema-instance"
xmlns="
http://www.frontrange.com/goldmine/xmlexport"
xsi:SchemaLocation="
http://www.frontrange.com/goldmine/xmlexport
gmschema.xsd" >
<header>
<goldmine_version>6.50.40704</goldmine_version>
<gmdbdef>
... Edited for display
</gmdbdef>
</header>
<account gm_recid="7AWRPEP),?[DX{J" gm_accountno="00012859969(BLV;XPab" >
<properties>
<property name="Address" db_name="ADDRESS1" >
<property_string>1234 Nowhere Drive</property_string>
</property>
<property name="Business" db_name="KEY2" >
<property_string>Electronics Supplier</property_string>
</property>
<property name="Callbkfreq" db_name="CALLBKFREQ" >
<property_string>0</property_string>
</property>
<property name="City" db_name="CITY" >
<property_string>San Luis Obispo</property_string>
</property>
<property name="Company" db_name="COMPANY" >
<property_string>Xtech and YTech Company</property_string>
</property>
<property name="Contact" db_name="CONTACT" >
<property_string>Maureen Smith</property_string>
</property>
<property name="Contact Type" db_name="KEY1" >
<property_string>Supplier</property_string>
</property>
<property name="Country" db_name="COUNTRY" >
<property_string>U.S.A.</property_string>
</property>
<property name="Createat" db_name="CREATEAT" >
<property_string>16:39</property_string>
</property>
<property name="Createby" db_name="CREATEBY" >
<property_string>LENV</property_string>
</property>
<property name="Createon" db_name="CREATEON" >
<property_string>20000128</property_string>
</property>
<property name="Dear" db_name="DEAR" >
<property_string>Maureen</property_string>
</property>
<property name="Last" db_name="LASTNAME" >
<property_string>Gaffney</property_string>
</property>
<property name="Lastcontat" db_name="LASTCONTAT" >
<property_string>12:11pm</property_string>
</property>
<property name="Lastconton" db_name="LASTCONTON" >
<property_string>20040922</property_string>
</property>
<property name="Lastdate" db_name="LASTDATE" >
<property_string>20041001</property_string>
</property>
<property name="Lasttime" db_name="LASTTIME" >
<property_string>13:10</property_string>
</property>
<property name="Lastuser" db_name="LASTUSER" >
<property_string>LENV</property_string>
</property>
<property name="Prevresult" db_name="PREVRESULT" >
<property_string>We're on the move!</property_string>
</property>
<property name="State" db_name="STATE" >
<property_string>CA</property_string>
</property>
<property name="Uscalever" db_name="USCALEVER" >
<property_string>0</property_string>
</property>
<property name="Zip" db_name="ZIP" >
<property_string>90210</property_string>
</property>
</properties>
<phone_numbers>
<phone international="0" type="FAX" source_tbl="CONTACT1" source_fld="FAX" >
<properties>
<property name="phone_number" >
<property_string>(905)555-5088</property_string>
</property>
</properties>
</phone>
<phone international="0" type="Phone1" source_tbl="CONTACT1" source_fld="PHONE1" >
<properties>
<property name="phone_number" >
<property_string>(905)555-6155</property_string>
</property>
</properties>
</phone>
</phone_numbers>
... Edited for display
</account>

Ron

Slinger1010 · Apr 10, 2007

OK I managed to figure out some of it but I'm still a little stumped. can you not nest for-each statements?

under properties there are multiple property.

This doesn't work

Code:

<?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:accounts">
			<xsl:value-of select="concat('&quot;',gm:account/@gm_recid,'&quot;,')"/>
			<xsl:value-of select="concat('&quot;',gm:account/@gm_accountno,'&quot;,')"/>
			<xsl:for-each select="gm:gmdata/gm:accounts/gm:account/gm:properties/gm:property">
				<xsl:value-of select="concat('&quot;',gm:account/@name,'&quot;,')"/>
				<xsl:value-of select="concat('&quot;',@db_name,'&quot;,')"/>
				<xsl:value-of select="concat('&quot;',gm:property_string,'&quot;,')"/>
			</xsl:for-each>
			<xsl:value-of select="$newline"/>
		</xsl:for-each>
		
	</xsl:template>
</xsl:stylesheet>

Am I close?

Ron

k5tm · Apr 10, 2007

Ron,

Can you clarify whether there is an <accounts> </accounts> element around all the <account> elements?

I am working the other issues. The property attribute technique, while very flexible, causes the XPath expressions to be a bit more complicated.

Tom Morrison

http://www.liant.com

Slinger1010 · Apr 10, 2007

There is <accounts> </accounts> around all the <account> elements.

Ron

k5tm · Apr 10, 2007

Code:

<?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: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[@name='Business']/gm:property_string,'&quot;,')"/>
    <xsl:value-of select="concat('&quot;',gm:properties/gm:property[@name='Company']/gm:property_string,'&quot;')"/>
    <xsl:value-of select="$newline"/>
</xsl:for-each>

</xsl:template>
</xsl:stylesheet>

Code:

"7AWRPEP),?[DX{J","00012859969(BLV;XPab","Electronics Supplier","Xtech and YTech Company"

Now the interesting thing is the XPath expressions for pulling values out of the properties:

[tt]gm

roperties/gm

roperty[@name='Company']/gm

roperty_string[/tt]

This is saying that you want the property_string element value for the property element for which the name attribute value is the string [tt]Company[/tt]. This demonstrates the ability to place an XPath predicate expression (the expression contained in the square brackets) on any node of the Xpath expression.

Does this help?

Tom Morrison

http://www.liant.com

Slinger1010 · Apr 10, 2007

Yes this helps quite a bit, thanks

using this as an example

Code:

<gmdata xmlns:xsi="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema-instance"[/URL]
 xmlns="[URL unfurl="true"]http://www.frontrange.com/goldmine/xmlexport"[/URL]
xsi:SchemaLocation="[URL unfurl="true"]http://www.frontrange.com/goldmine/xmlexport[/URL] gmschema.xsd" >
  <header>
    <goldmine_version>6.50.40704</goldmine_version>
    <gmdbdef>
      <gmdbfld flddbfname="CAL" fldname="ACCOUNTNO" fldtype="character" fldlen="20" ></gmdbfld>
      <gmdbfld flddbfname="CAL" fldname="ACONFIRM" fldtype="character" fldlen="3" ></gmdbfld>
      <gmdbfld flddbfname="CAL" fldname="ACTVCODE" fldtype="character" fldlen="3" ></gmdbfld>
	 <gmdbfld flddbfname="CONTACT1" fldname="ACCOUNTNO" fldtype="character" fldlen="20" ></gmdbfld>
      <gmdbfld flddbfname="CONTACT1" fldname="ADDRESS1" fldtype="character" fldlen="40" ></gmdbfld>
      <gmdbfld flddbfname="CONTACT1" fldname="ADDRESS2" fldtype="character" fldlen="40" ></gmdbfld>
    </gmdbdef>
  </header>
</gmdata>

I want to only carry over the contact1, yet my code isn't working(not getting anything but blank quotes).

Code:

<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>

Did I misuse this example you gave me?

Ron

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

XML noobie help

Programmer

Programmer

Programmer

Programmer

Technical User

Programmer

Technical User

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor