💽 Reading XML Attachments

Moving data without human repeating same work over and over again is something that can speed up a lot of processes, and also reduce amount of mistakes, and errors.

While JSON is really common for REST Api, there are still systems using SOAP, or just outputting XML as files, and we want to integrate with them too. Today, I'll show how to deal with reading XML in WEBCON.

There is already one article on this topic in the web here -> daniel-notes.de, but I'll bring something new, as this approach won't need any SDK - it's pure SQL, so no headache with updating (unless database schema will change).

Understanding our data

So let's start with the fact that all the attachments in WEBCON are stored in BPS_Content_Att database (at least that's default name). You can find there 2 tables:

• GlobalParameters
• WFAttachmentFiles

We are interested in WFAttachmentFiles, as here, all the files are stored, with Image type, or in another words - binary data.

The whole trick of this solution is that we exactly know what kind of data is inside this binary - string, exactly XML, so we can leverage this, and basically just CAST, and then Query with XPath. As Example we will work with simple XML structure like this:

<?xml version="1.0" encoding="utf-8"?>
<Invoice>
    <Company>Lumenn Ltd.</Company>
    <ID>INV/123</ID>
    <Positions>
        <Position>
            <Name>Light Bulb</Name>
            <Quantity>20</Quantity>
            <Price>5.34</Price>
        </Position>
        <Position>
            <Name>Chair</Name>
            <Quantity>2</Quantity>
            <Price>120.43</Price>
        </Position>
        <Position>
            <Name>Standing Desk</Name>
            <Quantity>2</Quantity>
            <Price>430.98</Price>
        </Position>
        <Position>
            <Name>Coffee Machine</Name>
            <Quantity>2</Quantity>
            <Price>80.00</Price>
        </Position>
    </Positions>
</Invoice>

SQL Queries

We have everything needed to prepare our SQL queries

• How data is stored
• Where it is stored
• What is the format/schema

I have 2 queries to use, first for getting single fields/values, and second for reading arrays. They will use Query, and Nodes function on XML type. Here is how they look like, with some explanation:

SELECT
    -- Query executes XQuery on the Content, which in this case will be our XML
    Content.query('/Invoice/Company/text()') As Company,
    Content.query('/Invoice/ID/text()') As ID
FROM (
    SELECT 
        --Here we first cast Image to varbinary - it's needed because direct cast from Image to XML is not possible.
        --After it's just varbinary, we are able to cast it directly to XML, it's not really encoded/encrypted.
        CAST(CAST([ATF_Value] AS varbinary(MAX)) AS XML) AS Content
    FROM 
        [BPS_Content_Att].[dbo].[WFAttachmentFiles]
    WHERE
        ATF_WFDID = {WFD_ID} AND
        ATF_IsDeleted = 0
) As Result(Content) -- Regular named output column

SELECT
    Position.value('(Name/text())[1]', 'VARCHAR(MAX)') AS PositionName,
    Position.value('(Quantity/text())[1]', 'INT') AS Quantity,
    Position.value('(Price/text())[1]', 'DECIMAL(10, 2)') AS Price
FROM (
    SELECT
        --Here we first cast Image to varbinary - it's needed because direct cast from Image to XML is not possible.
        --After it's just varbinary, we are able to cast it directly to XML, it's not really encoded/encrypted.
        CAST(CAST([ATF_Value] AS VARBINARY(MAX)) AS XML) AS Content
    FROM 
        [BPS_Content_Att].[dbo].[WFAttachmentFiles]
    WHERE
        ATF_WFDID = {WFD_ID} AND
        ATF_IsDeleted = 0
) AS Result(Content)
CROSS APPLY Content.nodes('/Invoice/Positions/Position') AS P(Position);
-- This is a little bit tricky to explain, but i'll try.
-- Our SELECT returns 1 row with the XML content, and we wan't to have more rows than one
-- Content.nodes returns multiple rows, but we have to join them, that's where CROSS APPLY comes in.
-- You could also look up at this SO question -> https://stackoverflow.com/questions/23498284/why-is-cross-apply-needed-when-using-xpath-queries


-- This way is easier to understand, but WEBCON doesn't like DECLARE statements, so i prefer CROSS APPLY.
DECLARE @Content AS XML = (
    SELECT
        CAST(CAST([ATF_Value] AS VARBINARY(MAX)) AS XML) AS Content
    FROM 
        [BPS_Content_Att].[dbo].[WFAttachmentFiles]
    WHERE
        ATF_WFDID = {WFD_ID} AND
        ATF_IsDeleted = 0
)

SELECT
    Position.value('(Name/text())[1]', 'VARCHAR(MAX)') AS PositionName,
    Position.value('(Quantity/text())[1]', 'INT') AS Quantity,
    Position.value('(Price/text())[1]', 'DECIMAL(10, 2)') AS Price
FROM 
    @Content.nodes('/Invoice/Positions/Position') AS P(Position);

There is a bonus query - it's basically a connection of those above, maybe you'll need it:

SELECT
    Company.value('(Company/text())[1]', 'VARCHAR(MAX)') AS Company,
    Company.value('(ID/text())[1]', 'VARCHAR(MAX)') AS ID,
    Position.value('(Name/text())[1]', 'VARCHAR(MAX)') AS PositionName,
    Position.value('(Quantity/text())[1]', 'INT') AS Quantity,
    Position.value('(Price/text())[1]', 'DECIMAL(10, 2)') AS Price
FROM (
    SELECT
        CAST(CAST([ATF_Value] AS VARBINARY(MAX)) AS XML) AS Content
    FROM 
        [BPS_Content_Att].[dbo].[WFAttachmentFiles]
    WHERE
        ATF_WFDID = {WFD_ID} AND
        ATF_IsDeleted = 0
) AS Result(Content)
CROSS APPLY Content.nodes('/Invoice') AS T(Company)
CROSS APPLY Company.nodes('Positions/Position') AS P(Position);

If you will use it, remember to check if WHERE conditions apply to your use case, i don't really have to focus on them, as it's not PROD environment.

I've used a bit of GPT help here being honest, as I couldn't find right explanation for me on the internet, you can check out conversation here. I'm interested in how people are using AI, so I'm sharing my use, as it might bring you some ideas :)

WEBCON Configuration

We have now our queries, so the last step is to implement them in some solution.
Our application will be simple, with just right amount of fields, to test examples in practice.

We want to read our XML into connected fields, I'll prepare 2 action templates (1 for updating list, one for updating regular fields), and 1 automation for this. Also I had to configure MSSQL connection to the attachment database, it's being used by both of the actions. Default bps_user doesn't exist in BPS_Content_Att database.

I'll add this Automation on top bar under 'Admin' button visible only in Admin mode. This solution requires to have existing document in database - it won't work when attaching file to new form, and reading it on first path used (but you could create some technical step for it). And that's it - our import button is working.

You can find code and application in this repo:

GitHub - lumenn/webcon-xml-read: SQL Queries, and PoC application for article on reading XML data to WEBCON at blog.lumenn.pl

SQL Queries, and PoC application for article on reading XML data to WEBCON at blog.lumenn.pl - GitHub - lumenn/webcon-xml-read: SQL Queries, and PoC application for article on reading XML data to W…

GitHublumenn

Or download just application for WEBCON 2023.1.1.89 from file below: