AX suggestion: XML Columns and XML indexes

In AX we have the following datatypes we can use in table fields.

But in MS SQL server there is a XML data type. With the XML data type we can store XML documents in the SQL Server database. And we can create columns and variables of the xml type and store XML instances in them. we can also create XML-indexes that speeds up searching on the XML contents. As seen here, I have manually added a XML column, and a XML primary index.

The Xml datatype allows us to perform several operations on the xml data from within t-sql. Although this is not very fast, it’s often better than round-tripping and doing the xml parsing in an application like Dynamics AX. Take a look at the following blog-post for a sample SQL- Querying XML attributes from XML Columns.

Why would this be interesting for the Dynamics AX ?

Let’s say we want to have the possibility to dynamically add new fields and information to an item/customer/BOM, but we don’t want to make customizations. One possibility would then be to have a XML field or a related table that contains a XML datatype column. In this column new fields and values could be stored inside the XML. By having some generic code that is extracting the XML values into fields or computed columns, would mean that we could provide a generic way of letting the user interact with dynamic fields, and that the user could add the fields as wanted on the fly without customizations. And still have the search, sort and filter capabilities.

I would like to use it for storing metadata like searchable retail product attributes. Since the XML format is a bit generic additional actions and events could also be stored inside the XML document. This could be functional triggers and workflows to be executed. It could even be specified down to the lowest record level.

So what is the difference from having XML’s in an ordinary text field ? One difference is the ability to use XML indexes, and to have search, filter and sort capabilities on the values stored inside the XML, without parsing the entire XML. More information on this is available here. Then the user could work with dynamic fields as it was real fields.

The first step in exploring this possibility is to have XML columns and XML indexes available in AX.

So my question to Microsoft is “Can we get the XML datatype and the ability to create XML indexes in AX 7.X ?

At least I think it is an interesting idea for the future.

Advertisements

2 thoughts on “AX suggestion: XML Columns and XML indexes

  1. Hi Kurt,
    had you got an answer yet? I am very interested in what are the plan of Microsoft regardeing the questiong “Can we get the XML datatype and the ability to create XML indexes in AX 7.X ?”
    Regards,
    Paul

    Like

    • Hi Paul. Yes, there where a feedback from Microsoft at connect.microsoft.com (by Markus). And here is the Microsoft feedback;

      We have heard this request before, and it certainly has a lot of merit; we have had similar requests to support spatial types, that would allow people to easily deal with distances between coordinates on the earth.

      We feel that the usage scenarios are still quite specialized. The work involved is substantial: We would need an XML type, obviously, and that type would have to be represented correctly as a string type in X++. Then you would need to be able to author queries against the XML in X++, which would mean significant changes in the syntax for all the data access statements. All in all, this is a big chunk of work, and I feel the value is not proportional with the effort.

      Note, that there are workarounds you can use, if you are really hard pressed: Nothing prevents you from writing X++ code with DirectSQL to create tables with XML fields for instance. It is not something I would recommend doing (since it is unsafe, probably introducing more problems than it solves), but it is certainly possible.

      Take care and happy DAX’ing

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s