CODEWITHSUNDEEP
sqlsql-serverxmlt-sqlxquery
Cuchy
Cuchy

Reputation: 25

XML File Translate with SQL query

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:

enter image description here

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?

Upvotes: 1

Views: 205

Answers (2)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22157

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

Upvotes: 1

Serg
Serg

Reputation: 22811

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

Upvotes: 0

Related Questions