Parsing a field containing XML

[jasonwood]

G'day,

I am setting up my database to process credit cards using XML SOAP. When I submit the transaction, I get a response back that looks like the following (which goes into a field).

Is there an easy way to reference the values here to get them into fields? In particular, I probably need to isolate the values for Authorization_Num, Error_Number, Error_Description, Transaction_Error, Transaction_Approved, Bank_Resp_Code, Bank_Message, etc.

Server: Microsoft-IIS/6.0

Connection: close

X-Aspnet-Version: 1.1.4322

Date: Mon, 25 Sep 2006 22:49:32 GMT

Cache-Control: private, max-age=0

Content-Length: 3565

X-Powered-By: ASP.NET

Content-Type: text/xml; charset=utf-8

<?xml version="1.0" encoding="utf-8"?>



xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" 

xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" 

xmlns:tns="http://secure2.e-xact.com/vplug-in/transaction/rpc-enc/" 

xmlns:types="http://secure2.e-xact.com/vplug-in/transaction/rpc-enc/encodedTypes" 

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

xmlns:xsd="http://www.w3.org/2001/XMLSchema">









A00990-01

cadsmwp

00

123.56

0

4111111111111111

66741266

ET4124

0808

BOB VILLA

0

0

0

0

0

INV55555

0

Processed by: E-xact Transaction Gateway - Version 8.4.0 B15 Copyright 2006

{Batch(Overflow)}

0



false

true

00

Transaction Normal

000

APPROVED                       

748



09254124

Paymentech Merchant Plug-in Test Store

44 King Street West

Toronto

ON

Canada

M5H 1H1

www.smwp.com





========== TRANSACTION RECORD =========

 

Paymentech Merchant Plug-in Test Store

44 King Street West

Toronto, ON M5H 1H1

www.smwp.com

 

TYPE: Purchase

 

ACCT: Visa             $123.56 CAD

 

CARD NUMBER : ############1111

TRANS. REF. : INV55555

CARD HOLDER : BOB VILLA

EXPIRY DATE : xx/xx

DATE/TIME   : 25 Sep 06 15:41:23

REFERENCE # : 00000008 748 M

AUTHOR.#    : ET4124

 

      Approved - Thank You 000

 

SIGNATURE







_______________________________________

[Martin Brändle]

If this data is already in a field, you can use calculation fields that use the Position() and Middle() functions to extract the values.

Otherwise, you have first to strip off the part before the XML declaration. Then you get a well-formed XML file that you can transform with XSLT, and you can extract the required data with proper XPath expressions . If you have FMSA you can create a -new or -edit query that adds your data. If you have FM Pro or Advanced only you can import a file in FMPXMLRESULT grammar.

[Fenton]

I don't know how many things you need from the xml, nor your xsl skills, so can't say whether an xsl stylesheet is the way to go. If very many, it's what I'd do. You will still need to post-process the CTR element in FileMaker however, as there are a couple pieces of data which are only in that field, ACCT, DATE/TIME. Most of the rest of CTR is already in the other xml elements above and can be gotten directly via xsl.

I'm a bit pressed for time today, but here is a basic xsl stylesheet, to get that one element. It does not have the FileMaker stuff however. You'll have to look at examples or do a raw xml export to see what that looks like. Anyway:

<?xml version="1.0" encoding="utf-8"?>



xmlns:xsd="http://www.w3.org/2001/XMLSchema" 

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

xmlns:types="http://secure2.e-xact.com/vplug-in/transaction/rpc-enc/encodedTypes" 

xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">

[jasonwood]

It sounds like using Position() and Middle() functions to isolate the data would be easiest, given that I know nothing about xsl.

Thanks!

[beverly]

Yep, that's the way I *used* to do it. Now we have custom functions...

ExtractData ( XML ; Attribute ; Instance )

Andy Knasinski, NRG Software

http://www.nrgsoft.com/

You can find this custom function already done by Andy:

ExtractData

From there you can change the function if you wish for other "features".

[angelleye]

I've got this custom function working...kind of. Here is a sample of the XML response I'm trying to parse:

<?xml version="1.0" encoding="utf-8"?>

AckCodeType

string

string

ErrorClassificationCodeType

token

string

string

SeverityCodeType

string

string

dateTime

string

When I use

ExtractData ( $eBayMarkPaidResponse ; "ErrorCode" ; 1)

It returns the Error Code just fine. However, when I use:

ExtractData ( $eBayMarkPaidResponse ; "ShortMessage" ; 1)

I don't get anything.

[angelleye]

Ah, I just had my variable name mis-typed. It's working now.

Though I am a little confused by the fact that you have to select an instance of the node to select with this custom function. Does anybody know of one that allows me to parse xml data more like XMLDOM within ASP or PHP? Where I can make XPath selections and loop through nodes instead of this instance thing...which won't work very well in a lot of cases.

[Fenton]

Well, to "loop through nodes" you could go back to the method of using FileMaker Import XML, with an xsl stylesheet. I think there are also plug-ins which can do this; Troi Text anyway. A plug-in or tool which lets you run some kind of command-line tool would also work; though I don't know what you'd use for Windows. Since FileMaker XML Import works you don't usually need other tools. Though you'd have to first remove any non-xsl headers.

[Martin Brändle]

Check the schubec PHPowered Plugin:

http://www.schubec.com/en_fmphpplugin.php