How to manipulate the attribute value in xml data and insert into database

Question

How to manipulate the language attribute value in the following example, say to select EN1.

DECLARE @xml XML

SET @xml = N'<email><account language="English" /></email>'

SELECT T.C.value('@language', 'nvarchar(100)')
FROM @xml.nodes('email/account') T(C)

I am thinking something like this

SELECT T.C.value(CONCAT(SUBSTRING(@language, 1,2), '1'), 'nvarchar(100)')
FROM @xml.nodes('email/account') T(C)

`CONCAT` and `SUBSTRING` should be **around** the expression using the `value` method , not inside the method. — Thom A, Jul 01 '22 at 12:38

score 0 · Answer 1 · answered Jul 02 '22 at 14:07

0

Many thanks to @Larnu, the following worked

SUBSTRING(SELECT T.C.value('@language', 'nvarchar(100)'), 1,2) + '1'
FROM @xml.nodes('email/account') T(C)

answered Jul 02 '22 at 14:07

InquisitiveLad

309
3
16

How to manipulate the attribute value in xml data and insert into database

1 Answers1