0

I am having problems converting XML that I receive from an external system (i.e. I cannot change the format) into something that I can insert into my MS SQL table.

I need to extract the different language texts from the XML file.

This is the code I currently have:

`DECLARE @articleNumber         NVARCHAR  (30);
DECLARE @articleShortDescription_DEU    NVARCHAR (100);
DECLARE @articleShortDescription_ENG    NVARCHAR (100);

SELECT 
  @articleNumber = ParamValues.myObj.value('(ItemMasterHeader/ItemID/ID)[1]',   'nvarchar  (30)'),
  @articleShortDescription_DEU = ParamValues.myObj.value('(ItemMasterHeader/Description)[1]', 'nvarchar (100)'),
  @articleShortDescription_ENG = ParamValues.myObj.value('(ItemMasterHeader/Description/@languageID)[1]','nvarchar (100)')
FROM @xmlData.nodes('/DataArea/ItemMaster') AS ParamValues(myObj);  

INSERT INTO Article (ArticleNumber, ArticleShortDescription_DEU, ArticleShortDescription_ENG)
SELECT @articleNumber, @articleShortDescription_DEU, @articleShortDescription_ENG`

Here is the XML file I have:

<ItemMaster>
<ItemMasterHeader>
  <ItemID>
    <ID accountingEntity="999" lid="lid://infor.ln.test_xxx" variationID="1111111">9999999</ID>
  </ItemID>

  <Description>Main text</Description>

  <Description languageID="de_DE">DE text</Description>
  <Description languageID="es_ES">ES text</Description>
  <Description languageID="fr_FR">FR text</Description>
  <Description languageID="it_IT">IT text</Description>
  <Description languageID="pl_PL">PL text</Description>
  <Description languageID="en_US">US text</Description>
  </ItemMasterHeader>

The @articleShortDescription_DEU contains the "Main text" as expected and @articleShortDescription_ENG contains "de_DE" (which is also expected). I need the value of the "en_US" text (in this case "US text") in the field @articleShortDescription_ENG.

Any ideas as to how I can do this?

grizzthedj
  • 7,131
  • 16
  • 42
  • 62
Ursus Schneider
  • 447
  • 1
  • 6
  • 22

1 Answers1

0

I found the answer here: How to get a particular attribute from XML element in SQL Server

basically this:

@articleShortDescription_ENG = ParamValues.myObj.value('(ItemMasterHeader/Description[@languageID="en_US"])[1]' , 'nvarchar (100)')

Ursus Schneider
  • 447
  • 1
  • 6
  • 22