Extracting XML from Keyfax tables

Help to pull data in a readable format.

We are often asked, particularly by integration engineers, how to access information relating to Startup or Export data. Typically, data is held in XML documents which can be difficult to read. Using SQL Server Management Services (SSMS) it is possible to view either Session or History data.

Session Data

Here we are looking at session data from the seSessionValues table. Other tables holding XML content are syOrder (repair orders) and syTaskHistory (repairs and enquiries).

To retrieve the latest Startup XML the following provides clickable links.

SELECT DateTime, GUID, Name, CONVERT(xml, REPLACE(CAST(Data AS NVARCHAR(MAX)), ' encoding="utf-8"', ''))
FROM seSessionValues WITH (NOLOCK)
WHERE Name = 'Startup'
ORDER BY DateTime DESC

Run the results into a Grid and the hyperlinks will be presented; clicking each will open the formatted XML document in a new tab:

seSessionValues sample data

Export XML can be extracted as follows:

SELECT DateTime, GUID, Name, CONVERT(xml, REPLACE(CAST(Data AS NVARCHAR(MAX)), ' encoding="utf-8"', ''))
FROM seSessionValues WITH (NOLOCK)
WHERE Name = 'EXPORT_XML'
ORDER BY DateTime DESC

History Data

Depending on your configuration, this will involve two main tables syOrders and syTaskHistory:

SELECT * FROM syOrder WITH (NOLOCK) ORDER BY Created DESC
 or
SELECT * FROM syTaskHistory WITH (NOLOCK) ORDER BY Created DESC