Replacing values in the XML nodes to reference nodes correctly using XQUERY and filter required values

Question

I've have a table which contains XML data with several nodes in one of the columns. Something similar to the below.

<SendWorkOrders xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                creationDateTime="2021-06-05T10:16:09.713+12:00"
                requestCorrelationId="63cbec24c87a1bfa88999ef373e"
                xmlns="http://mycompany.co.nz/customerservices/servicepartner/workorder">
    <WORKORDERSET>
        <WORKORDER action="Replace">
            <WONUM>123456</WONUM>
            <DESCRIPTION>My work order</DESCRIPTION>
        </WORKORDER>
    </WORKORDERSET>
</SendWorkOrders>

I want to change the format to something like below in order to use XQUERY and filter required nodes.

<SendWorkOrders>
<WORKORDERSET>
<WORKORDER>
<WONUM>123456</WONUM>
<DESCRIPTION>My work order</DESCRIPTION>
</WORKORDER>
</WORKORDERSET>
</SendWorkOrders>

Here's more details.

I have a table called 'WorkOrderLogs' which has 4 columns with EventId,Source,Timestamp,SendWorkOrders. SendWorkOrders column stores the raw XML from customer as mentioned in the first code snippet. I'm trying to reference specific nodes in the XML value using the query below.

SELECT e.EventId,e.Timestamp,e.SendWorkOrder,e.Source,
X.Y.value('(WONUM)[1]','VARCHAR(20)') as WONUMBER,
X.Y.value('(DESCRIPTION)[1]','VARCHAR(256)') as DESCRIPTION
From WorkOrderLogs e
OUTER APPLY e.SendWorkOrders.nodes('SENDWORKORDERS/WORKORDERSET/WORKORDER/WONUM') as X(Y)

It returns NULL values as my root node/namespace contains additional information of the schemas and other details. So, I'm thinking if i am able to format the value to something simple then i can achieve what i want here.

I managed write a query to pick the nodes, but failed to reference them correctly. Root nodes contain schema and additional information unique for each message. I couldn't simplify it to target the required nodes. I'm fairly new to T-SQL, so any help/pointers are highly appreciated.

Thanks in advance.

Update:

Thank you so much Yitzhak Khabinsky. It worked like a charm for one use case. But, I got another problem now. The namespace exists in child nodes.

Sample value is something like this.

<OrderNotification xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
        <POSKI xmlns="http://MyCompany.co.nz">12345</POSKI>
        <BUKRS xmlns="http://MyCompany.co.nz">ABCD</BUKRS>
        <PRCTR xmlns="http://MyCompany.co.nz">1324</PRCTR>
    </OrderNotification>

Query I'm trying is

DECLARE @OrderLogs TABLE ( OrderNotification XML);
INSERT INTO @ProjectLogs (ProjectNotification)
(SELECT Info
      
  FROM [MainLogging].[dbo].[JobLogs]

  where EventId = 'XXXXXXXX'
  and Message ='OrderNotification');

  WITH XMLNAMESPACES (DEFAULT 'http://mycompany.co.nz')
  SELECT c.value('(POSKI/text())[1]','VARCHAR(20)') AS ORDER
    , c.value('(BUKRS/text())[1]','VARCHAR(256)') AS DESCRIPTION
    FROM @ProjectLogs
    CROSS APPLY OrderNotification.nodes('/OrderNotification') AS t(c);

While asking a question, you need to provide a **minimal reproducible example**. Please refer to the following link: https://stackoverflow.com/help/minimal-reproducible-example Please provide the following: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in #1 above. (4) Your SQL Server version (SELECT @@version;) — Yitzhak Khabinsky, Jun 06 '21 at 01:56
the XML root node contains namespaces. That's normal for XML. Please provide ##1-4 like I mentioned in the previous comment with your real XML for few rows. There is no need to remove namespaces. Just show what you are trying to query. — Yitzhak Khabinsky, Jun 06 '21 at 02:11
Sorry about that. Added more details with the query I'm trying. Thanks — sasi, Jun 06 '21 at 03:37

score 1 · Accepted Answer · answered Jun 06 '21 at 03:48

Please try the following solution.

It is a minimal reproducible example. You copy it to SSMS as-is, and it works.

It handles the default namespace via WITH XMLNAMESPACES clause.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, SendWorkOrders XML);
INSERT INTO @tbl (SendWorkOrders) VALUES
(N'<SendWorkOrders xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                creationDateTime="2021-06-05T10:16:09.713+12:00"
                requestCorrelationId="63cbec24c87a1bfa88999ef373e"
                xmlns="http://mycompany.co.nz/customerservices/servicepartner/workorder">
    <WORKORDERSET>
        <WORKORDER action="Replace">
            <WONUM>123456</WONUM>
            <DESCRIPTION>My work order</DESCRIPTION>
        </WORKORDER>
    </WORKORDERSET>
</SendWorkOrders>');
-- DDL and sample data population, end

WITH XMLNAMESPACES (DEFAULT 'http://mycompany.co.nz/customerservices/servicepartner/workorder')
SELECT ID
    , c.value('(WONUM/text())[1]','VARCHAR(20)') AS WONUMBER
    , c.value('(DESCRIPTION/text())[1]','VARCHAR(256)') AS DESCRIPTION
FROM @tbl
    CROSS APPLY SendWorkOrders.nodes('/SendWorkOrders/WORKORDERSET/WORKORDER') AS t(c);

Output

+----+----------+---------------+
| ID | WONUMBER |  DESCRIPTION  |
+----+----------+---------------+
|  1 |   123456 | My work order |
+----+----------+---------------+

Thank you so much Yitzhak Khabinsky. I just another issue with one of the notification logs. I've updated my post with the use case. Can you please help? — sasi, Jun 06 '21 at 05:45
One question per post. You need to create a new post for a new question. ##1-4 — Yitzhak Khabinsky, Jun 06 '21 at 05:54
pardon my ignorance. I've posted a new question. Thanks a lot. — sasi, Jun 06 '21 at 06:14

Replacing values in the XML nodes to reference nodes correctly using XQUERY and filter required values

1 Answers1