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

Yitzhak Khabinsky · Accepted Answer · 2021-06-06 06:27:52Z

2

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

answered Jun 6, 2021 at 6:27

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sasi Over a year ago

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

Collectives™ on Stack Overflow

Replacing XML namespace to use XQUERY

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related