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?

1 Answer 1

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

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.