-1

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.

Improve this question
7
  • Please show us the attempt you made so we can help you fix it Commented Sep 4, 2024 at 9:35
  • Are your values valid XML? Commented Sep 4, 2024 at 9:37
  • 3
    That looks like XML; SQL Server supports XQuery, so why not use that? Don't treat your data as a string if it's XML, treat it as xml. Commented Sep 4, 2024 at 9:38
  • between and what does that means? Can you provide the actual value you expect to extract? Commented Sep 4, 2024 at 9:40
  • From the example i need the value: 2011284 Tried this SELECT id, url, SUBSTRING (cast(url as text), CHARINDEX('</artikel>',10),5 ) AS substring FROM job Commented Sep 4, 2024 at 9:45

2 Answers 2

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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:

Screenshot of the experiment

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.

Improve this answer

3 Comments

I modified your string as follows: declare @url varchar(4096) = (select url from job where url like '%<artikel>%'); SELECT SUBSTRING(@url, charindex('<artikel>', @url) + 9, charindex('</artikel>', @url) - charindex('<artikel>', @url) - 9); Result: Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. what did i made wrong?
@TomZaugg you have multiple records inside your url record and each has a SUBSTRING. When you assign the result of this query as a value to your $url variable, then you treat is as a scalar, that is, a single value. Yet, the plurality of your results will not allow you to do so. Can you explain in plain words how you want to use later url? Whether you want all the substrings or you want only one from them? How would you later use @url? Can you show the code where you attempt to use it as an edit to your question?
@TomZaugg, please edit your question, and provide the URL column data type, and more complete data samples for it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.