XML File Translate with SQL query

Question

I have a Swift Message encoded with SQL query using encoding="utf-16 - I used below code:

CAST(REPLACE(CAST(Content AS VARCHAR(MAX)), 'encoding="utf-16"', '')  AS XML) AS Content

Content of my XML structure looks like this:

What I want to achieve

I have a temporary table with multiple XML Files in this structure and I am trying to write a SQL query which will show me data in standard structure as a table

SELECT
    DateTimeCreated,
    t.value('(ApplicationHeaderBlock_Output/MessageType/text())[1]', 'varchar(100)') AS MessageType,
    e.value('(SequenceC/Date_C_98A/Date/text())[1]', 'DATE') AS XDTE,
    e.value('(SequenceC/Date_C_98A//Date/text())[1]', 'DATE') AS RDTE
FROM 
    #MTFILE
CROSS APPLY 
    Content.nodes('//SWIFTHeader') AS a(t)
CROSS APPLY 
    Content.nodes('//SWIFT_CATEGORY5_MT566') AS b(e)

DROP TABLE #MTFILE

Problem and what I want to achieve

I'm not sure how to modify my current SQL query to achieve below results from XML file, because when I'm trying to specify path from XML file you can see it is same (from screenshot structure).

My point is to achieve and see result as below:

| XDTE        | RDTE           |
| --------    | -------------- |
| 20210310    | 20211012       |

If somebody could please advise?

Don't use pictures to show your data or code. Use plain text. — Serg
– Serg, Commented Mar 19, 2021 at 11:26

Yitzhak Khabinsky · Accepted Answer · 2021-03-19 14:02:26Z

1

Because you didn't provide a minimal reproducible example, we need to assume that your SELECT ... statement is legit.

Here is your answer.

SQL

SELECT
    DateTimeCreated,
    t.value('(ApplicationHeaderBlock_Output/MessageType/text())[1]', 'varchar(100)') AS MessageType,
    e.value('(SequenceC/Date_C_98A[Qualifier/text()="XDTE"]/Date/text())[1]', 'DATE') AS XDTE,
    e.value('(SequenceC/Date_C_98A[Qualifier/text()="RDTE"]/Date/text())[1]', 'DATE') AS RDTE
FROM 
    #MTFILE
CROSS APPLY 
    Content.nodes('//SWIFTHeader') AS a(t)
CROSS APPLY 
    Content.nodes('//SWIFT_CATEGORY5_MT566') AS b(e);

answered Mar 19, 2021 at 14:02

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

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

3 Comments

Cuchy Over a year ago

This resolved my problem completely. Thanks a lot

Yitzhak Khabinsky Over a year ago

@Cuchy, glad to hear that the proposed solution is working for you. Please don't forget to mark it as Answer.

Yitzhak Khabinsky Over a year ago

@Cuchy, please up-vote the following suggestion: feedback.azure.com/forums/908035-sql-server/suggestions/…

Serg · Accepted Answer · 2021-03-19 11:47:12Z

Extract Qualifier and pivot. I use conditional aggregation for pivoting. Your DBMS may have a special PIVOT tool.

select DateTimeCreated, MessageType,
     max(case when q='XDTE' then d end) as XDTE,
     max(case when q='RDTE' then d end) as RDTE
from (
    -- original query plus `Qualifier`
    -- hopefully it extracts data correctly
    Select DateTimeCreated,
       t.value('(ApplicationHeaderBlock_Output/MessageType/text())[1]', 'varchar(100)') as MessageType,
       e.value('(SequenceC/Date_C_98A/Qualifier/text())[1]', 'DATE') as q,
       e.value('(SequenceC/Date_C_98A/Date/text())[1]', 'DATE') as d
    FROM #MTFILE
    CROSS APPLY Content.nodes('//SWIFTHeader') as a(t)
    CROSS APPLY Content.nodes('//SWIFT_CATEGORY5_MT566') as b(e)
) t
group by DateTimeCreated, MessageType

Collectives™ on Stack Overflow

XML File Translate with SQL query

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related