I have values such as below in a column of a table in SQL Server 2022.
What I need is to grab with a select statement the value between <artikel>
and </artikel>
Tabelname: JOB
Column: URL
..<ersatzteilOperations><mode>V</mode><artikel>2011284</artikel><belegticket>je mapelle velogstell</belegticket><artaugabeneingang>V</artaugabeneingang></ersatzteilOperations>
Can anybody please help me? I saw same explanations but did not understand it.
I tried with substring and charindex, but didn't get the value I need.
What I expect is the value between and in a separate column. I need this value to compare it with other tables.
A minimal reproducible example is not provided, so I am shooting from the hip.
The answer is following the same minimal reproducible example pattern. Just copy it to SSMS as-is, run it, and see the results.
SQL
-- DDL and sample data population, start
DECLARE @tbl_job TABLE (id INT IDENTITY PRIMARY KEY, URL NVARCHAR(MAX));
INSERT INTO @tbl_job (URL) VALUES
(N'../masterdata/artikel.asmx anp_ErsatzteilOperationsMail() <ersatzteilOperations><mode>erledigt</mode><artikel>702440</artikel></ersatzteilOperations>'),
(N'../masterdata/artikel.asmx anp_ErsatzteilOperations() <ersatzteilOperations><mode>V</mode><artikel>2010622</artikel><belegticket>CWU-DiesDas</belegticket><artaugabeneingang>V</artaugabeneingang></ersatzteilOperations>');
-- DDL and sample data population, end
SELECT id
, c.value('(artikel/text())[1]', 'VARCHAR(20)') AS artikel
FROM @tbl_job AS t
CROSS APPLY (SELECT TRY_CAST(STUFF(URL, 1, CHARINDEX('<', URL) - 1, '') AS XML)) AS t1(x)
CROSS APPLY x.nodes('/ersatzteilOperations') AS t2(c);
Output
id | artikel |
---|---|
1 | 702440 |
2 | 2010622 |