Replacing XML namespace to use XQUERY

Question

I have a sample input looking like below in one of the columns in the table.

<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>

I need to shred this XML to extract the values from the message. However, because of the namespace in the child nodes, i'm unable to do so. Following a suggestion from my previous question, I wrote my query as below.

DECLARE @OrderLogs TABLE ( OrderNotification XML);

INSERT INTO @ProjectLogs (OrderNotification)
    (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);

It's not returning any output because I'm not referencing the nodes correctly. Can anyone help please?

Thanks in advance

score 2 · Accepted Answer · answered Jun 06 '21 at 06:27

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, OrderNotification XML);
INSERT INTO @tbl (OrderNotification ) VALUES
(N'<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>');
-- DDL and sample data population, end

WITH XMLNAMESPACES ('http://MyCompany.co.nz' AS ns1)
SELECT ID
    , c.value('(ns1:BUKRS/text())[1]','VARCHAR(20)') AS BUKRS
    , c.value('(ns1:POSKI/text())[1]','VARCHAR(256)') AS POSKI
    , c.value('(ns1:PRCTR/text())[1]','VARCHAR(256)') AS PRCTR
FROM @tbl
    CROSS APPLY OrderNotification.nodes('/OrderNotification') AS t(c);

Output

+----+-------+-------+-------+
| ID | BUKRS | POSKI | PRCTR |
+----+-------+-------+-------+
|  1 | ABCD  | 12345 |  1324 |
+----+-------+-------+-------+

I can't thank you enough. My use case is a bit more complex than I detailed above. But, i was able to wrap the results and insert them into a table to execute the xquery. It's successful and i got the results needed. Thanks a lot for your help!! — sasi, Jun 06 '21 at 08:10

Replacing XML namespace to use XQUERY

1 Answers1