Get values from xml nodes within sql statement -- with string to xml manipulation

Question

I have string data with xml format in "Input" column from which I need specific values of nodes.

As an example:

I need every "error_text_1" value from each "error id".

{
 "xml" : "<?xml version='1.0'?>

 <error_protokoll xmlns:SOAP-ENV.....>

 <header>  
 <source>machine</source> 
 </header>

 <error_list>

 <error id='0'>
 <error_text_1>error0</error_text_1> 
 </error>

 <error id='1'>
 <error_text_1>error1</error_text_1>
 </error>

 </error_list>

 </error_protokoll>"
}

The following sql statement returns only the "error_text_1" value of id=0.

SELECT top (10)

XML_INPUT.value('(error_protokoll/error_list/error/error_text_1)[1]', 'varchar(200)') error

FROM 
(
 convert(xml, SUBSTRING( REPLACE(INPUT, '{
 "xml" : "<?xml version=''1.0''?>', ''), 0 , len(REPLACE(INPUT, '{
 "xml" : "<?xml version=''1.0''?>', ''))-3)) as XML_INPUT

  FROM  [Storage].[ods].[table]

) a

Please, see that I need to process first the "Input" column value to remove the "{}" brackets and the "xml" : ...xml version=''1.0..." part to have a correct xml structure.

This was the workaround to be able to get node value out of it.

Instead of only the first item, I'd like to have all of them.

Could you please help how to solve this?

While asking a question, you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Jan 5, 2022 at 13:06

persian-theme · Accepted Answer · 2022-01-05 11:45:02Z

0

I modified your xml syntax. I think he had a little problem. The following query fetches your information.

DECLARE @XML XML = '<?xml version="1.0"?>
<error_protokoll>
 <header>  
 <source>machine</source> 
 </header>
 <error_list>
   <error id="0">
      <error_text_1>error0</error_text_1> 
   </error>
   <error id="1">
     <error_text_1>error1</error_text_1>
   </error>
 </error_list>
</error_protokoll>';
 
SELECT
      B.rec.value('(@id)[1]','int') AS id,
      B.rec.value('(error_text_1/text())[1]','nvarchar(100)') AS error_text_1 
FROM @XML.nodes('/error_protokoll/error_list') A(evnt)
OUTER APPLY A.evnt.nodes('error') B(rec)

Demo in db<>fiddle

Or If you want to read the data from the table column

SELECT
      B.rec.value('(@id)[1]','int') AS id,
      B.rec.value('(error_text_1/text())[1]','nvarchar(100)') AS error_text_1 
 

FROM tb
OUTER APPLY tb.xmlRec.nodes('/error_protokoll/error_list') A(evnt)
OUTER APPLY A.evnt.nodes('error') B(rec)

Demo in fiddle

edited Jan 5, 2022 at 11:45

answered Jan 5, 2022 at 11:39

persian-theme

6,6483 gold badges9 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Thom A Over a year ago

The braces are around the XML are intentional. Otherwise this would be a duplicate of their prior question. The problem is that they didn't explain in it that their data was malformed.

Kristan Over a year ago

Unfortunately, I cannot modfy just like that since that is a value in a row of the Input column.

persian-theme Over a year ago

I do not know exactly what your stored data looks like if you enter two rows of your data to be checked

persian-theme Over a year ago

If you can enter exactly the data stored in sql to check.

Collectives™ on Stack Overflow

Get values from xml nodes within sql statement -- with string to xml manipulation

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related