Read Dynamics NAV Table Metadata with SQL

Question

I would like to be able to read the Dynamics NAV 2013 Table Metadata directly from the SQL Server database without requiring the NAV Development Environment.

I can view the binary SQL "image" BLOB columns with a query like the following (filter as appropriate with WHERE clause):

SELECT 
    o.[Name],
    m.[Object Type],
    m.[Metadata], -- XML Metadata
    m.[User Code], -- C# Metadata
    m.[User AL Code] -- C/AL Metadata
FROM [Navision].[dbo].[Object Metadata] AS m
JOIN [Navision].[dbo].[Object] AS o
ON m.[Object ID] = o.[ID]
AND o.[Company Name] = 'YourCompanyName'
AND o.[Type] = 0 -- 0 is NAV Table Object Type

I can save the binary data from the [Metadata], [User Code], and [User AL Code] off to files using .Net code or a quick script with SQL driver. I have tried using 7-zip to decompress, a hex editor to view, and the Cygwin "file" command to detect these BLOB file types.

Unfortunately I can't figure out how to decode or decompress the binary data into a readable or usable format. Until I can use the data in those fields directly, I must open NAV Dev Environment and use the Object Designer to view the comma-separated OptionString properties for zero-based lookup list drop-down menu (each item in the list is stored in the back-end database as an integer - 0 for first item, 1 for second, etc). The string values are not present in a SQL lookup table, but NAV does put them in the table metadata blobs.

This is the missing link for me to completely support my NAV users as a DBA without needing a NAV developer to lookup these number-to-name NAV custom field mappings for me. I can then lookup these list values and create matching SQL CASE statements or custom lookup tables as needed.

Once I have this piece I should be able to create advanced SQL views, queries, reports, and tools with no need for access to the Dynamics NAV front-end user or developer tools.

Please let me know if you have knowledge of the binary data format used for these NAV Object Metadata blob properties. Any advice on how to convert to a readable or usable format would be helpful.

Answer 1

Updated basic version incorporating changes from below - thanks @James Kelly for the XML processing and @Kristof for fixing the conversion issue.

Can confirm, the problem with very large XML docs before was using SUBSTRING on the image column before converting it to varbinary. When those operations are reversed, it works. The table I tested with returns a ~160k character XML document. Prior, it was stopping around 80k characters.

select 
    CAST
    (
        CONVERT
        (
            varchar(max), 
            DECOMPRESS
            (
                CONVERT(varbinary(max), 0x1F8B0800000000000400) 
                + 
                SUBSTRING(convert(varbinary(max), om.[Metadata]), 5, 2147483647)
            ) 
        )  as xml
    ) AS [XML]
from 
    [dbo].[Object Metadata] om 
where 
    om.[Object Type] = 1
    and om.[Object ID] = @obj_id

Apologies for absolutely raising this thread from the dead, but I finally got this working for and thought others might find it useful:

select
  CONVERT(
    varchar(max)
    , DECOMPRESS(
        CONVERT(varbinary(max), 0x1F8B0800000000000400) 
        + 
        CONVERT(varbinary(max), SUBSTRING([Metadata],5,8000))
      )
  )
from 
  [dbo].[Object Metadata] 
where 
  [Object Type] = 1 and [Object ID] = @objId

This would work out of the box if DECOMPRESS (SQL Server 2016+) had an option to ignore the gzip header but, alas, it does not. I checked a few tables and the header didn't change, so I just stuffed it on the front.

Hope this helps someone else!

Answer 2

First of all thanks for the script!

I also ended up to have some problems with it. For me on a NAV2015 Table 27 and 5363 won't work...I can see that it is able to get meta data decompressed, but it looks like the string ends at one point leaves the XML incomplete and as such fails...

I solved this now with the following adjustment:

DECOMPRESS( CONVERT(varbinary(max), 0x1F8B0800000000000400) + CONVERT(varbinary(max), SUBSTRING(CONVERT(varbinary(max), om.[Metadata]),5,2147483647)) ) ) AS [XML],

Answer 3

Wrote this based on the input above from Brandon Roberts (thanks Brandon!) I am getting some tables that do not decompress properly and I am thinking it is possibly an issue with the header.

WITH cte AS ( select om.[Object Type], om.[Object ID], TRY_CAST(CONVERT( varchar(max) , DECOMPRESS( CONVERT(varbinary(max), 0x1F8B0800000000000400) + CONVERT(varbinary(max), SUBSTRING(om.[Metadata],5,80000)) ) ) AS XML) AS [XML], CONVERT( varchar(max) , DECOMPRESS( CONVERT(varbinary(max), 0x1F8B0800000000000400) + CONVERT(varbinary(max), SUBSTRING(om.[Metadata],5,80000)) ) ) AS [Raw] from [dbo].[Object Metadata] om where om.[Object Type] = 1 )

SELECT TOP 1000 XML.value('declare namespace ns="urn:schemas-microsoft-com:dynamics:NAV:MetaObjects"; (/ns:MetaTable/@ID)[1]', 'nvarchar(100)') AS [Table No_], XML.value('declare namespace ns="urn:schemas-microsoft-com:dynamics:NAV:MetaObjects"; (/ns:MetaTable/@Name)[1]', 'nvarchar(100)') AS [Table Name], FieldData.value('@ID', 'int') AS [Field No_], FieldData.value('@Name', 'nvarchar(100)') AS [Field Name], FieldData.value('@Datatype', 'nvarchar(100)') AS [Data Type], FieldData.value('@DataLength', 'int') AS [Data Length], FieldData.value('@OptionString', 'nvarchar(max)') AS [Option String], XML FROM cte CROSS APPLY XML.nodes('declare namespace ns="urn:schemas-microsoft-com:dynamics:NAV:MetaObjects"; /ns:MetaTable/ns:Fields/ns:Field[@FieldClass="Normal" and @Enabled="1"]') AS Fields(FieldData);

Answer 4

Why not use webservices and get the data you need without having to do anything fancy?

Users can then use PowerPivot for Excel and create their own reports.

You could also use the Excel Add-In for NAV and just export data to excel directly from NAV and then refresh from within Excel.

Answer 5

I was able to get an answer for the format of these Metadata binary fields from the author of the deV.ch - man vs. code, Dynamics NAV & C# .NET blog. Based on the reverse engineering by devch, we determined that the first four bytes (32 bits) of these fields are used by NAV to store a "magic number" that determines the custom NAV Blob type.

In the case of these metadata fields, the NAV Compressed Blob-Type magic number is 0x02457d5b (hex). In order to use the standard .Net DeflateStream to Decompress, just throw away those first four magic-number bytes and then process the rest of the stream with DeflateStream as usual.

I was able to successfully test this process with .Net, now I plan to test with Python or some other non-Microsoft deflate tools to see if the deflate implementation follows the industry standard. Thanks again to devch for the article that led to this solution: Accessing Compressed Blobs from outside NAV (NAV2013) (Revisited).

Update: tested with Python zlib and it works! Standards-compliant Deflate algorithm is used once the custom NAV Blob-type magic number is removed. Here's some sample code (Python):

# Example Using Python 3.x
import zlib, sys, struct

# NAV custom Blob-Type identifier (first 4 bytes)
magic = struct.unpack('>I',sys.stdin.buffer.read(4))[0]
print('magic number = %#010x' % magic, file=sys.stderr)
# Remaining binary data is standard DEFLATE without header
input = sys.stdin.buffer.read()
output = zlib.decompress(input,-15)
sys.stdout.buffer.write(output)

Use something like the following to test:

python -u test.py < Input_Meta.blob > Output_Meta.txt

Of course the .Net DeflateStream works after removing the first four bytes as well. This example is just to show that you're not limited to using .Net languages.

Answer 6

It is possible to extract metadata from this table through Nav and as I can see it is stored as plain text but in binary field. It can be saved to file using simple MemoryStream (in Nav it is called OutSteram). So for table number 3 I get following XML:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<MetaTable xmlns="urn:schemas-microsoft-com:dynamics:NAV:MetaObjects" ID="3" CaptionML="ENU=Payment Terms;RUS=Условия платежа" DataPerCompany="1" Name="Payment Terms" LookupFormID="4" DataCaptionFields="1,5">
    <Fields>
        <Field ID="1" Datatype="Code" DataLength="10" Enabled="1" FieldClass="Normal" Name="Code" CaptionML="ENU=Code;RUS=Код" BlankNumbers="DontBlank" BlankZero="0" SignDisplacement="0" Editable="1" NotBlank="1" Numeric="0" DateFormula="0" ClosingDates="0" Title="0" AutoIncrement="0" ValidateTableRelation="1" TestTableRelation="1" ExtendedDatatype="None"/>
        <Field ID="2" Datatype="DateFormula" Enabled="1" FieldClass="Normal" Name="Due Date Calculation" CaptionML="ENU=Due Date Calculation;RUS=Расчет срока оплаты" BlankNumbers="DontBlank" BlankZero="0" SignDisplacement="0" Editable="1" NotBlank="0" Numeric="0" DateFormula="0" ClosingDates="0" Title="0" AutoIncrement="0" ValidateTableRelation="1" TestTableRelation="1" ExtendedDatatype="None"/>
        <Field ID="3" Datatype="DateFormula" Enabled="1" FieldClass="Normal" Name="Discount Date Calculation" CaptionML="ENU=Discount Date Calculation;RUS=Расчет даты скидки" BlankNumbers="DontBlank" BlankZero="0" SignDisplacement="0" Editable="1" NotBlank="0" Numeric="0" DateFormula="0" ClosingDates="0" Title="0" AutoIncrement="0" ValidateTableRelation="1" TestTableRelation="1" ExtendedDatatype="None"/>
        <Field ID="4" Datatype="Decimal" Enabled="1" FieldClass="Normal" Name="Discount %" CaptionML="ENU=Discount %;RUS=Скидка (%)" BlankNumbers="DontBlank" BlankZero="0" SignDisplacement="0" Editable="1" MinValue="0" MaxValue="100" NotBlank="0" Numeric="0" DateFormula="0" ClosingDates="0" Title="0" AutoIncrement="0" ValidateTableRelation="1" TestTableRelation="1" ExtendedDatatype="None"/>
        <Field ID="5" Datatype="Text" DataLength="50" Enabled="1" FieldClass="Normal" Name="Description" CaptionML="ENU=Description;RUS=Описание" BlankNumbers="DontBlank" BlankZero="0" SignDisplacement="0" Editable="1" NotBlank="0" Numeric="0" DateFormula="0" ClosingDates="0" Title="0" AutoIncrement="0" ValidateTableRelation="1" TestTableRelation="1" ExtendedDatatype="None"/>
        <Field ID="6" Datatype="Boolean" Enabled="1" FieldClass="Normal" Name="Calc. Pmt. Disc. on Cr. Memos" CaptionML="ENU=Calc. Pmt. Disc. on Cr. Memos;RUS=Расчет скидки оплаты по кредит-нотам" BlankNumbers="DontBlank" BlankZero="0" SignDisplacement="0" Editable="1" NotBlank="0" Numeric="0" DateFormula="0" ClosingDates="0" Title="0" AutoIncrement="0" ValidateTableRelation="1" TestTableRelation="1" ExtendedDatatype="None"/>
    </Fields>
    <Keys>
        <Key Enabled="1" Key="Field1" MaintainSQLIndex="1" MaintainSIFTIndex="1" Clustered="1"/>
    </Keys>
    <FieldGroups>
        <FieldGroup GroupID="1" GroupName="DropDown" GroupFields="Field1,Field5,Field2"/>
    </FieldGroups>
</MetaTable>

Suppose this is what you want.

Code writing it to file in Nav will look like this:

ObjectMetadata:Record(Object Metadata)
Code:BigText
File:File       
CodeStream:InStream     
FileStream:OutStream        

ObjectMetadata.INIT;

IF ObjectMetadata.GET(1,3) THEN
 BEGIN
  ObjectMetadata.CALCFIELDS(Metadata);
  File.CREATE('C:\temp\Code.txt');
  File.CREATEOUTSTREAM(FileStream);

  clear(codestream);
  ObjectMetadata."Metadata".CREATEINSTREAM(CodeStream);
  Code.READ(CodeStream);
  Code.WRITE(FileStream);

  file.close();
 END;

Now you have options: try to do same thing in SQL/.Net (I'm not keen with it) or you can ask your Nav developer to make some kind of batch job that will periodically (or on demand) process all tables' matadata and save it to external table/file/whatever which you can access from SQL.