how to filter a multinlingual xml

Question

Hi,

I have a multi node XML file which is being used as a source to extract data into a database. I am using SSIS to achieve the same.
The XML file is a multilingual XML which has values in multiple languages , for example, please see below.

I want to filter only the language "en" from the file and load into a table.

Any help appreciated! Thanks in advance!

Label Action="ADDORUPDATE" lang="de">Level 0</Label>
<Label Action="ADDORUPDATE" lang="no">Level 0</Label>
<Label Action="ADDORUPDATE" lang="fi">Level 0</Label>
<Label Action="ADDORUPDATE" lang="ru">Level 0</Label>
<Label Action="ADDORUPDATE" lang="pt">Level 0</Label>
<Label Action="ADDORUPDATE" lang="bg">Level 0</Label>
<Label Action="ADDORUPDATE" lang="hr">Level 0</Label>
<Label Action="ADDORUPDATE" lang="fr">Level 0</Label>
<Label Action="ADDORUPDATE" lang="hu">Level 0</Label>
<Label Action="ADDORUPDATE" lang="sk">Level 0</Label>
<Label Action="ADDORUPDATE" lang="sl">Level 0</Label>
<Label Action="ADDORUPDATE" lang="sr">Level 0</Label>
<Label Action="ADDORUPDATE" lang="ptBR">Level 0</Label>
<Label Action="ADDORUPDATE" lang="sv">Level 0</Label>
<Label Action="ADDORUPDATE" lang="ko">Level 0</Label>
<Label Action="ADDORUPDATE" lang="zh-tw">Level 0</Label>
<Label Action="ADDORUPDATE" lang="el">Level 0</Label>
<Label Action="ADDORUPDATE" lang="enUS">Level 0</Label>
<Label Action="ADDORUPDATE" lang="en">Level 0</Label>
<Label Action="ADDORUPDATE" lang="it">Level 0</Label>
<Label Action="ADDORUPDATE" lang="es">Level 0</Label>
<Label Action="ADDORUPDATE" lang="zh">Level 0</Label>
<Label Action="ADDORUPDATE" lang="et">Level 0</Label>
<Label Action="ADDORUPDATE" lang="cs">Level 0</Label>
<Label Action="ADDORUPDATE" lang="ja">Level 0</Label>
<Label Action="ADDORUPDATE" lang="pl">Level 0</Label>
<Label Action="ADDORUPDATE" lang="da">Level 0</Label>
<Label Action="ADDORUPDATE" lang="nl">Level 0</Label>
<Label Action="ADDORUPDATE" lang="tr">Level 0</Label>

Answer 1

Hi @Jahnavi Sriram ,

The following links will be helpful:
Multilingual XML files
Filtering XML Columns using XQuery in SQL Server

Best Regards,
Mona

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.