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?
