sqlsql-serverselectsubstringcharindex

Need part of a string in column


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.


Solution

  • 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