Get values from xml nodes within sql statement

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

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

Looks like you have JSON containing XML, perhaps you should use OPENJSON or JSON_VALUE to retrieve it. Also not sure why you are removing the <?xml preamble, it's supposed to be there. Please give a minimal reproducible example containing the actual value you need to convert — Charlieface
– Charlieface, Commented Jan 5, 2022 at 9:40
@Charlieface, I edited the original post to show the original cell value which is in the Input column. Because of the "{ }" brackets and ""xml" : "<?xml version=''1.0''?>" I convert the text into xml. — Kristan
– Kristan, Commented Jan 5, 2022 at 10:11
I have rolled back that edit, as it invalidates my existing answer; this is severely frowned upon by the community. Don't be a Chameleon. — Thom A
– Thom A ♦, Commented Jan 5, 2022 at 10:18
Looks like CAST(JSON_VALUE(yourValue, '$.xml') AS xml) should do the trick — Charlieface
– Charlieface, Commented Jan 5, 2022 at 10:19

Thom A · Accepted Answer · 2022-01-05 09:06:19Z

1

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

answered Jan 5, 2022 at 9:06

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

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

6 Comments

Kristan Over a year ago

the problem is that I need first to convert string to xml, so I need to use your recommendation within the original statement, doing something like this: SELECT top (10) YC.e.value('(./error_text_1/text())[1]','varchar(200)') error_tesxt_1 FROM ( select cross apply convert(xml, SUBSTRING( REPLACE(INPUT, '{ "xml" : "<?xml version=''1.0''?>', ''), 0 , len(REPLACE(INPUT, '{ "xml" : "<?xml version=''1.0''?>', ''))-3)).nodes('error_protokoll/error_list/error')YC(e) FROM [Storage].[ods].[table] ) b

Thom A Over a year ago

"the problem is that I need first to convert string to xml" Why isn't your XML not xml in the first place, @Kristan ? That there is your problem. If you want to parse XML in T-SQL, then it needs to be xml.

Kristan Over a year ago

Yes, you are absolutely right but unfotunately here is some other information which violates the xml structure thus first I need to preprocess and convert it to xml. The "XML_INPUT" finally hass the right structure but with this I can only get single values as shown in the oiginal code, @Larnu.

Thom A Over a year ago

The XML in your question is valid XML, @Kristan . If you have XML stored as a (n)varchar that isn't valid XML, and you want to convert it to valid XML that is a different question; you'll need to ask a new question, with examples of the malformed XML. Though SQL Server's string manipulation is poor at best. You need to fix your data, and most likely that needs to be done at source.

Thom A Over a year ago

Please don't edit a question to invalidate existing answers, @Kristan . If the question has changed, ask a new question.

|

Collectives™ on Stack Overflow

Get values from xml nodes within sql statement

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related