Loop XML nodes and concatenate in SQL Server

Question

I have this XML structure, which I have to parse into SQL Server and create a Where condition:

<Columns>
    <Column name='Id' condition=' > ' value='20'>
    <Column name='salary' condition=' < ' value= 20000>
</Columns>

Number of column nodes can vary 2,3,4 etc.

Output should be like:

Id > 20 and salary < 20000

For this I have to loop the column nodes and concatenate the attributes in single where string.

Using following article I'm able to read the XML: http://blog.sqlauthority.com/2009/02/13/sql-server-simple-example-of-reading-xml-file-using-t-sql/

My main challenge is to read the XML in efficient manner.

Devart · Accepted Answer · 2015-11-30 13:52:47Z

2

DECLARE @xml XML = '
<Columns>
    <Column name="Id" condition="&gt;" value="20" />
    <Column name="salary" condition="&lt;" value="20000" />
</Columns>'

SELECT STUFF((
    SELECT ' AND [' + t.c.value('@name', 'SYSNAME') + '] ' + 
        t.c.value('@condition', 'SYSNAME') + ' ' + 
        t.c.value('@value', 'SYSNAME')
    FROM @xml.nodes('Columns/Column') t(c)
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 5, '')

result -

[Id] > 20 AND [salary] < 20000

answered Nov 30, 2015 at 13:52

Devart

120k23 gold badges172 silver badges189 bronze badges

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

Collectives™ on Stack Overflow

Loop XML nodes and concatenate in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related