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.
2 thoughts on “AX suggestion: XML Columns and XML indexes”
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 ?”
Hi Paul. Yes, there where a feedback from Microsoft at connect.microsoft.com (by Markus). And here is the Microsoft feedback;
Take care and happy DAX’ing