0

I'd like to ask if there is an easy way to iterate through my XML data using T-SQL.

My xml looks like:

<node_1>
  <set attribute4="text">
    <CONDS>
      <COND variable="a" value="text1" attribute3="text1"> //if a == text1 
        <COND variable="b" value="text1" attribute3="text1"> //(and)b == text 1(and)
          <COND variable="c" value="text1" attribute3="text1"/> //(and)c == text1 
          <COND variable="c" value="text1" attribute3="text1"/> //(or)c == text2
        </COND>
      </COND>
      <COND variable="a" value="text1" attribute3="text1"> //or a == text1
    </CONDS>
  </set>
</node_1>

I basically need to iterate through all <COND> nodes. These nodes are actually later on used as a SQL query condition (WHERE clause) and evaluate set of inputs. A <COND> element and its child represent an AND condition and a <COND> element which is at the same level is an OR condition.

My aim is to know how to iterate through these nodes.

I think I can achieve this using bunch of codes like openxml (to convert the whole xml to a table), cte (to get table and descendants), and pivot (to set those table rows of fields to columns) which is then used to evaluate my inputs. but this will require lots of loops and indicators for and/or conditions which makes it confusing and hard to maintain.

NOTE: node_1 is a sub element and can also be repeated

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
mailman
  • 11
  • 2
  • Iterating the XML is easy enough. Doing it while building a where clause "will require lots of loops and indicators for and/or conditions which makes it confusing and hard to maintain". There is no shortcut. – Mikael Eriksson Feb 27 '13 at 07:04
  • Look into this post :[Iterating through XML in T-SQL](http://stackoverflow.com/questions/5006230/how-do-i-iterate-through-the-nodes-of-a-xml-field-in-t-sql) – Maryam Arshi Feb 27 '13 at 13:48

0 Answers0