Thursday, January 22, 2009

RETS Data Formats Standard, Compact, Compact Decoded

When making requests to the RETS server, you have to specify the the Return Format. Your choices are STANDARD-XML, COMPACT and COMPACT-DECODED. On the surface, this seems like a pretty minor decision. By pulling each you will see that STANDARD-XML looks like what you picture an XML document to look like, nicely formatted, organized and nested.


COMPACT and COMPACT-DECODED are pretty similar, much more concise (read: smaller) formats. The data all comes in two XML elements, one has a delimited list of columns, the other is a delimited list of values. The decoded version simply includes the long name value for lookup fields as opposed to the value. So the field LivingRoomFlooring would have the value "C" in a COMPACT format, and the value "Carpet" in the COMPACT-DECODED format.



Here is a some information that could save you a lot of head scratching; Don't use STANDARD-XML. STANDARD-XML adheres to a strict XML DTD, only displaying predefined "Standard Name" fields. Existing MLS databases are anything but standard. So what does this mean? If you request STANDARD-XML you will get only the fields specified in the RETS standard names specification. In the case of MRED (the MLS I am working with) this amounts to maybe 20% of the data. When you query RETS for data (i.e. a listing) you can pass a SELECT field that specifies which fields you would like in your return, just like an SQL SELECT. First note on this, a SELECT statement will have no effect on a STANDARD-XML request because it will always return the same document format. The documentation indicates that if you do not provide a SELECT statement the server should return all fields (like a SELECT *). This has not been my experience. If I don't provide a SELECT I get a subset of the data. Loop over your metadata and explicitly select all of the fields and you have hit pay dirt.

One last topic for this post. Using COMPACT or COMPACT-DECODED you get a tab delimited list of the column names and a tab delimited list of the values. First reaction is to loop over the columns list while running a counter, output column name, then use a listGetAt on the value list using the counter value. This will not work due to null values, when you tell Coldfusion to address the string as a chr(9) delimited list, it ignores the null values in the value list which kills the relationship between the two lists. The key is to do a couple replace operation on the strings prior to treating them as lists. First replace chr(9) with a pipe (|), then replace "||" with "|?|" twice. Something like this...

listVals = myXML.RETS.DATA.XmlText;
listVals = replace(listVals,chr(9),'|','all');
listVals = replace(listVals,'||','|?|','all');
listVals = replace(listVals,'||','|?|','all');

Now you can treat listVals as a pipe delimited list that will have a "?" to represent null.

No comments: