Jump to content

Help needed getting correct xpath for xlst


Devin

This topic is 3094 days old. Please don't post here. Open a new topic instead.

Recommended Posts

We are being supplied xml file that we will need to import the data for orders.

I've uploaded the example file of the xml and the xslt that I'm using to try to use.

The issue I'm having is trying to grab anything that after the ItemOut like SupplierPartID or Description

Any Ideas? Thanks

============

            <ItemOut lineNumber="1" quantity="100" requestedDeliveryDate="2014-10-20T16:17:27.300-05:00">
                <ItemID>
                    <SupplierPartID>9069</SupplierPartID>
                </ItemID>
                <ItemDetail>
                    <UnitPrice>
                        <Money>0.0</Money>
                    </UnitPrice>
                    <Description>ANOW BENEFITS BROCHURE - NADD - ENGLISH &lt;9069&gt;</Description>
                    <UnitOfMeasure>PACK</UnitOfMeasure>
                    <Extrinsic name="costCenter"/>
                    <Extrinsic name="quantityMultiplier">100</Extrinsic>
                    <Extrinsic name="UnitQuantity">100</Extrinsic>
                    <Extrinsic name="PackSize">100</Extrinsic>
                    <Extrinsic name="PackQuantity">1</Extrinsic>
                </ItemDetail>
                <ShipTo>
                    <Address addressID="1082327">
                        <PostalAddress name="AcceptanceNOW c/o Ashley Furniture Homestore">
                            <DeliverTo>Sales Manager</DeliverTo>
                            <Street>9999 Western Blvd Suite A</Street>
                            <Street/>
                            <Street/>
                            <City>Jacksonville</City>
                            <State>NC</State>
                            <PostalCode>28546</PostalCode>
                            <Country>US</Country>
                        </PostalAddress>
                    </Address>
                </ShipTo>
            </ItemOut>

 

 

example.xslt

example.xml

Link to comment
Share on other sites

Neither Description (in ItemDetail) nor SupplierPartID (in ItemID) are attributes - so you cannot address them using the @ prefix. Remove it and it will work.

Unrelated to your question, but it looks like an order could contain more than one item. If so, you need to create a ROW for each one of these.

Link to comment
Share on other sites

based on your given XML, these are the important (and unique) full paths:

/ItemOut/@lineNumber
/ItemOut/@quantity
/ItemOut/@requestedDeliveryDate
/ItemOut/ItemID/SupplierPartID
/ItemOut/ItemDetail/UnitPrice/Money
/ItemOut/ItemDetail/Description
/ItemOut/ItemDetail/UnitOfMeasure
/ItemOut/ItemDetail/Extrinsic[@name='costCenter']
/ItemOut/ItemDetail/Extrinsic[@name='quantityMultiplier']
/ItemOut/ItemDetail/Extrinsic[@name='UnitQuantity']
/ItemOut/ItemDetail/Extrinsic[@name='PackSize']
/ItemOut/ItemDetail/Extrinsic[@name='PackQuantity']
/ItemOut/ShipTo/Address/@addressID
/ItemOut/ShipTo/Address/PostalAddress/@name
/ItemOut/ShipTo/Address/PostalAddress/DeliverTo
/ItemOut/ShipTo/Address/PostalAddress/Street[1]
/ItemOut/ShipTo/Address/PostalAddress/Street[2]
/ItemOut/ShipTo/Address/PostalAddress/Street[3]
/ItemOut/ShipTo/Address/PostalAddress/City
/ItemOut/ShipTo/Address/PostalAddress/State
/ItemOut/ShipTo/Address/PostalAddress/PostalCode
/ItemOut/ShipTo/Address/PostalAddress/Country

I'm with comment: you will likely have repeats AND I'm reasonably sure you haven't posted the complete XML (there may be other elements not supplied here (even within the ItemOut parent).

Also, the above paths are _absolute_, you would likely use relative paths (relative to '/ItemOut/'?) within the full XML.

a few places to learn about XPath:

http://www.w3schools.com/xsl/xpath_intro.asp

http://www.w3.org/TR/xpath/ (note, you need to use XPath 1.0)

https://en.wikipedia.org/wiki/XPath

beverly

Link to comment
Share on other sites

Thanks guys for your help.. This xml stuff is new to me. I will give it a try and see what I get.

I did post the full xml that I was given as a test file. I've already asked if an order will have more then 1 item as I would think it would.

 

Thanks

Link to comment
Share on other sites

sorry, yes, you posted a small sample of the XML (even in your attachment). What I meant was that there is a schema called cXML (which is what you have). If you research the 'rules' for this kind of document, you'll learn what repeats and where. It's best to account for all possibilities in the schema when creating your XSLT. If they don't happen to exist in your source XML, then they are ignored on import.

If you study my 'xpath' list, you'll find how I handled the "Extrinsic" element (which could be repeating, but can be unique fields - in your database). Also the "Street" element repeats, but again should be handled as I did with the [n] (showing the order) and into separate fields (or concatenated into one field with CR between the values).

Otherwise, you may get into very complex 'repeating' that necessitates related tables and multiple XSLT (along with one import/one XSLT/per table).

Study the XPath, study the cXML schema and go from there. :) 

Link to comment
Share on other sites

 It's best to account for all possibilities in the schema when creating your XSLT. If they don't happen to exist in your source XML, then they are ignored on import.

You are correct.. I was at first only thinking about the sample data set they supplied. Looking over the Doc's on cXML I'm going to take a step back and figure out how I can create the database that will be able to take in all parts of the cXML.

Being that cXML is a standard format, I would have thought someone would have all ready created an XSLT for it. :)

Thanks for your help. 

Link to comment
Share on other sites

Being that cXML is a standard format, I would have thought someone would have all ready created an XSLT for it.

Possibly, but since this is an IMPORT into your database - your schema is going to be different than my schema (in FMP-speak that's fields and tables.). The XSLT is the document that "maps" between the XML source and the XML result (in this case, for import is the FMPXMLRESULT). Your XSLT would be totally invalid for import into my database if the fields are different or placed in related (or non-related) tables.  The XPATHS for the import could even be different based on the relative path for repeats that we each might choose.

Does that make sense?

Link to comment
Share on other sites

Your XSLT would be totally invalid for import into my database if the fields are different or placed in related (or non-related) tables.

Not necessarily. If the format is well-designed and follows the accepted data model for invoicing, then you could also design a "standard" XSLT stylesheet (or stylesheets) for importing the data into Filemaker. The differences between the target databases would then be just a matter of choosing which fields to import from all of the fields being offered by the format. There is practically no difference between this and importing from some "standard" invoicing solution created in Filemaker. You merely need to map their source fields to your target fields.

Link to comment
Share on other sites

Not necessarily. If the format is well-designed and follows the accepted data model for invoicing, then you could also design a "standard" XSLT stylesheet (or stylesheets) for importing the data into Filemaker. The differences between the target databases would then be just a matter of choosing which fields to import from all of the fields being offered by the format. There is practically no difference between this and importing from some "standard" invoicing solution created in Filemaker. You merely need to map their source fields to your target fields.

This was what I was thinking, I understand that the mapping would need to be resolved first, but because cXML is written in such away you do not need to know when and what they would include, so you will need to at least check for all the options for importing. Am I wrong in this logic?

Would I need to create 3 XSLT to handle the import into 3 tables?

1. For the Header and OrderRequestHeader 

2. For each of the ItemOut in case their is more then 1.

3. For the Extrinsic for each ItemOut.

What I understand about the Extrinsic is that it can have any name and value so I would not be able to call it by a known name. Seem like a catch all place for an item.

 

Link to comment
Share on other sites

 

I understand that the mapping would need to be resolved first,

No, actually the mapping would be resolved last. The purpose of the stylesheet would be to provide all possible options for the mapper to choose from.

 

 

Would I need to create 3 XSLT to handle the import into 3 tables?

I am not familiar with the cXML schema, so I can only speak of invoicing generally. The common data model has three basic tables -  Invoices, LineItems and Products - with optionally more, e.g. Clients, Addresses, Product Options, etc. For the basic three tables, you could have either three stylesheets, or a single one that produces a "flat" line items table.

 

2. For each of the ItemOut in case their is more then 1.

This question raises a red flag. Hopefully, in your solution there is only one table for Lineitems? Otherwise you have greater issues than this.

 

What I understand about the Extrinsic is that it can have any name and value so I would not be able to call it by a known name.

Well, yes and no. The XSLT can present every <Extrinsic> element as a field whose name is the same as the name attribute, and contains the value of the element. You as the Filemaker developer consuming the XML are expected to know these names in advance by getting them from your data provider. If there's no agreement between the two of you on this, then of course this cannot work.

Link to comment
Share on other sites

comment appears to disagree with my assessment and then proceeds to explain how complex "a standard" might be to import. :)

I still say another's db may be far different from mine and a 'standard XSLT' would need so much massaging (over and above changing the 'mapped fields'), that I'd start over. Or I may be importing into  client's db and NOT have the ability to alter the db in a way that matches the 'standard'. 

You know the comic is true!:

https://xkcd.com/927/

 

Link to comment
Share on other sites

This topic is 3094 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.