CODEWITHSUNDEEP
sql-serverxml
donstack
donstack

Reputation: 2715

Loop XML nodes and concatenate in SQL Server

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.

Upvotes: 0

Views: 380

Answers (1)

Devart
Devart

Reputation: 121922

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

Upvotes: 2

Related Questions