Dynamics Community Forum Thread Details

Export Product Model Query from BLOB to readable format with X++/SQL

Hello all

We are trying to get our configurable products exported to a 3rd party sales tool.

We know how to export a product configuration model as an xml from the UI.
The only problem is that the query which defines the fields and limitations each product has is in binary format and not readable.

Below is an SQL code to to see the query from SQL and even see some of the data it consists.
Just replace [YOUR PRODUCT MODEL RECID with the recId you get when going to the Product information management -> Common -> Product configuration models and selecting Record info with 2nd mouse button over some of the models.

-- PRODUCT MODEL

SELECT
  T5.QUERY,
  T4.EXPRESSION,
  CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), T5.QUERY)) AS TEXT
FROM
  -- T1 = PRODUCT CONFIGURATION MODEL
  PCPRODUCTCONFIGURATIONMODEL T1
JOIN
  ECORESCATEGORY T2
  ON  T1.ROOTCOMPONENTCLASS = T2.RECID
JOIN
  -- T3 = ALL CONSTRAINTS
  PCCOMPONENTCONSTRAINT T3
  ON  T2.RECID = T3.COMPONENTCLASS
JOIN
  -- T4 = CONSTRAINT
  PCCONSTRAINT T4
  ON  T3.CONSTRAINT_ = T4.RECID
LEFT JOIN
-- T5 = TABLE CONSTRAINTS
  PCTABLECONSTRAINTDEFINITION T5
  ON  T4.TABLECONSTRAINTDEFINTION = T5.RECID

WHERE
  T1.RECID = [YOUR PRODUCT MODEL RECID]

For example, the above code gives us the query like this from one of our models (exactly like the export product model saves it in the xml file):

0x07FD305F0100004A012F270000110001E649030000000A4DE90300000000840457004D00440049006E00760065006E0074005400610062006C00650045007800700061006E0064006500640031000000110001E803340057004D00440049006E00760065006E0074005400610062006C00650045007800700061006E0064006500640031005F0031000000E209300057004D00440049006E00760065006E0074005400610062006C00650045007800700061006E0064006500640031000000094DE8030000F319000000920402001100010000FFFFFFFFFFFFFFFF9B04FFFF9A04FFFF0157004D00440049006E00760065006E0074005400610062006C00650045007800700061006E00640065006400310000004900740065006D004900640000003200300034003200310034000000E803000000000000000100000000000001FFFFFFFF009005000000000000000000000000000000000000000000000000000000000000FF

In addition the code gives us the expression constraints and the query in semi readable format as TEXT field (where we can see some of the itemId's used in the range).

Is there any way to get that query shown/saved as readable xml or other structured format, or is it Ax's own binary format that cannot be exported out in any other way?

And yes, we have tried the methods presented here:
http://abraaxapta.blogspot.com/2011/06/accessing-dynamics-ax-containers-from.html?m=1
https://ashirokikh.com/converting-business-central-blob-to-text-with-sql-clr/

Quick Links