Friday, 13 September 2013

Using XML to generate row value constructors comparison?

Using XML to generate row value constructors comparison?

I am trying to generate the row value constructors like the following
example.
where
( (A = 3 and B = 8 and C = '2001-12-10' and D >= 'XCS')
or (A = 3 and B = 8 and C > '2001-12-10')
or (A = 3 and B > 8)
or (A > 3)
)
and
( (A = 9 and B = 4 and C = '2002-06-01' and D <= 'CCC')
or (A = 9 and B = 4 and C < '2002-06-01')
or (A = 9 and B < 4)
or (A < 9)
)
However, the following script doesn't generate the above code yet. How to
write the xquery?
declare @sql varchar(max);
declare @x xml = '
<Cols>
<Column>
<COLUMN_NAME>A</COLUMN_NAME>
<LQuote />
<RQuote />
<Lower>3</Lower>
<Higher>9</Higher>
</Column>
<Column>
<COLUMN_NAME>B</COLUMN_NAME>
<LQuote />
<RQuote />
<Lower>8</Lower>
<Higher>4</Higher>
</Column>
<Column>
<COLUMN_NAME>C</COLUMN_NAME>
<LQuote>''</LQuote>
<RQuote>''</RQuote>
<Lower>2001-12-10</Lower>
<Higher>2002-06-01</Higher>
</Column>
<Column>
<COLUMN_NAME>D</COLUMN_NAME>
<LQuote>''</LQuote>
<RQuote>''</RQuote>
<Lower>XCS</Lower>
<Higher>CCC</Higher>
</Column>
</Cols>';
set @sql = replace(Replace(@x.query('<sql>where
(~{
for $r in //Column
return
<sql>or ({for $c in //Column return <s>{$c/COLUMN_NAME} =
{$c/LQuote}{$c/Lower}{$c/RQuote} and </s>}~)
</sql>
})
and
(~{
for $r in //Column
return
<sql>or ({for $c in //Column return <s>{$c/COLUMN_NAME} =
{$c/LQuote}{$c/Higher}{$c/RQuote} and </s>}~)
</sql>
})
</sql>').value('.', 'nvarchar(max)'), 'and ~', ''), '~or', '')
print @sql
Current output of the code.
where
( (A = 3 and B = 8 and C = '2001-12-10' and D = 'XCS' )
or (A = 3 and B = 8 and C = '2001-12-10' and D = 'XCS' )
or (A = 3 and B = 8 and C = '2001-12-10' and D = 'XCS' )
or (A = 3 and B = 8 and C = '2001-12-10' and D = 'XCS' )
)
and
( (A = 9 and B = 4 and C = '2002-06-01' and D = 'CCC' )
or (A = 9 and B = 4 and C = '2002-06-01' and D = 'CCC' )
or (A = 9 and B = 4 and C = '2002-06-01' and D = 'CCC' )
or (A = 9 and B = 4 and C = '2002-06-01' and D = 'CCC' )
)

No comments:

Post a Comment