Get values from xml nodes within sql statement-CodePudding

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".

<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>

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

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

Could you please help how to solve this?

CodePudding user response：

You need to use nodes in the FROM so that you get 1 row per error element, then you can get the value of error_text_1 for each one:

DECLARE @xml xml = '<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 X.e.value('(./error_text_1/text())[1]','varchar(200)') AS error_text_1
FROM @xml.nodes('error_protokoll/error_list/error')X(e);

If the XML is in a table, your FROM would look like:

FROM dbo.YourTable YT
     CROSS APPLY YT.YourColumn.nodes('error_protokoll/error_list/error')YC(e);