Need part of a string in column

Question

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.

Answer 1

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

Answer 2

First, let's see how this should work with XQuery:

DECLARE @XML XML = '<root><ersatzteilOperations><mode>V</mode><artikel>2011284</artikel><belegticket>je mapelle velogstell</belegticket><artaugabeneingang>V</artaugabeneingang></ersatzteilOperations></root>';
SELECT @XML.query('/root/mode/artikel')

I encapsulated your XML into a root tag and I expect it to work. I was not able to test this, because I do not have SQL Server available at this point and the online fiddle failed due to

SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

That is, if you vie for this solution, make sure QUOTED_IDENTIFIER is properly set in your RDBMS.

As about your try of

SELECT SUBSTRING(yourcolumn, charindex('<artikel>', yourcolumn) + 9, charindex('</artikel>', yourcolumn) - charindex('<artikel>', yourcolumn) - 9) 
from yourtable

Let's ponder on this for a bit:

• SUBSTRING has an expression, a start and a length parameter, that is, from your expression, which is yourcolumn, you specify the index of the start of the substring you want to get (1-based index) and the number of characters as length
• CHARINDEX is also 1-based
• start was specified as charindex('<artikel>', yourcolumn) + 9, where you have searched for <artikel> in the string and while it is technically correct to pass 9 as the number of characters you want to jump over, I strongly recommend to use LEN, so if your next reuse of this code will search for the inner text of some other field, then you will not have to count the character number to jump over, so it would be better to do something like charindex('<artikel>', yourcolumn) + LEN('<artikel>') instead
• you specified charindex('</artikel>', yourcolumn) - charindex('<artikel>', yourcolumn) - 9) as the length, which is correct, because you get the difference between the positions of the start and the closing of the tag and you subtract the length of the start tag to get only the inner text, again, I recommend the use of LEN instead of the 9 that you have used

I have tested your query in a fiddle, via

declare @yourcolumn varchar(4096) = '<ersatzteilOperations><mode>V</mode><artikel>2011284</artikel><belegticket>je mapelle velogstell</belegticket><artaugabeneingang>V</artaugabeneingang></ersatzteilOperations>';
SELECT SUBSTRING(@yourcolumn, charindex('<artikel>', @yourcolumn) + 9, charindex('</artikel>', @yourcolumn) - charindex('<artikel>', @yourcolumn) - 9);

and it correctly yielded the expected 2011284, as you can see from the screenshot too:

Now, if this did not work for you, the reason for your problem could be of a zillion of different nature. You will need to make sure your connection is correct and other queries are executed successfully. You will need to make sure you have typed in your column names, table name and function names correctly and passed the parameters you wanted. If you have an error, study carefully what the error says to you. Think of cases when yourcolumn is a null, or it has no artikel tag or has multiple artikel tags. Make sure that your code yields the expected results for all these possible inputs.