来自XML的动态查询构造循环条件？

Question

我需要从xml构造我的sql查询的部分条件。

我有一个类似XML：

    <ROOT>
      <PARAMETROS>
        <USU_LOGIN>yleon</USU_LOGIN>
        <USU_NOMBREPRIMERO>Yerusha</USU_NOMBREPRIMERO>
        <USU_APELLIDOPRIMERO>Leon</USU_APELLIDOPRIMERO>
        <USU_EMAIL>[e-mail]</USU_EMAIL>
        <USU_FECHACREACION>20130510</USU_FECHACREACION>
        <USU_CODICIONES1 TIPO="MC">AND USU_ID=4</USU_CODICIONES1>
        <USU_CODICIONES2 TIPO="MC">AND USU_ID=5</USU_CODICIONES2>
        <USU_CODICIONES3 TIPO="HG">AND USU_ID=9</USU_CODICIONES3>
        ...
        <USU_CODICIONESN TIPO="MC">AND USU_ID=50</USU_CODICIONESN>
      </PARAMETROS>
    </ROOT>

因此，我需要提取属性为TIPO =“ MC”的条件；我使用以下代码：

SELECT txt = T.Item.value('data(.)', 'varchar(255)')
FROM   @xml.nodes('/ROOT/PARAMETROS/*') AS T(Item)
WHERE T.Item.value('data(@TIPO)', 'varchar(255)')='MC'

现在，假设我的查询是：

Select * from USUARIOS
where 1=1 USU_CODICIONES1 USU_CODICIONES2 .. USU_CODICIONESn

我需要替换循环XML的“ @@ USU_CODICIONESX”，但不使用游标，也许要使用cte。

预期结果是：

Select * from USUARIOS
where 1=1 AND USU_ID=4 AND USU_ID=5 .. AND USU_ID=50

Answer 1

您需要动态创建一个SQL语句，然后运行该命令

DECLARE @xml xml =
    '<ROOT>
       <PARAMETROS>
        <USU_LOGIN>yleon</USU_LOGIN>
        <USU_NOMBREPRIMERO>Yerusha</USU_NOMBREPRIMERO>
        <USU_APELLIDOPRIMERO>Leon</USU_APELLIDOPRIMERO>
        <USU_EMAIL>[e-mail]</USU_EMAIL>
        <USU_FECHACREACION>20130510</USU_FECHACREACION>
        <USU_CODICIONES1 TIPO="MC">AND USU_ID=4</USU_CODICIONES1>
        <USU_CODICIONES2 TIPO="MC">AND USU_ID=5</USU_CODICIONES2>
        <USU_CODICIONES3 TIPO="HG">AND USU_ID=9</USU_CODICIONES3>
        <USU_CODICIONESN TIPO="MC">AND USU_ID=50</USU_CODICIONESN>
      </PARAMETROS>
    </ROOT>'

DECLARE @query nvarchar(max) = 
  'SELECT * FROM USUARIOS where 1=1 USU_CODICIONES1 USU_CODICIONES2 USU_CODICIONESn',
        @dsql nvarchar(max)

;WITH cte AS
 (        
  SELECT T.Item.value('fn:local-name(.)', 'varchar(255)') AS cond,
         T.Item.value('data(.)', 'varchar(255)') AS NewCond,
         ROW_NUMBER() OVER(ORDER BY 1/0) AS rn
  FROM   @xml.nodes('/ROOT/PARAMETROS/*') AS T(Item)  
  WHERE T.Item.value('data(@TIPO)', 'varchar(255)')='MC'
  ), cte2 AS
 (
  SELECT rn, cond, newCond, REPLACE(@query, cond, NewCond) AS NewQuery
  FROM cte
  WHERE rn = 1
  UNION ALL
  SELECT c.rn, c.cond, c.newCond, REPLACE(c2.NewQuery, c.cond, c.NewCond)
  FROM cte c JOIN cte2 c2 ON c.rn = c2.rn + 1
  ) 
  SELECT TOP 1 @dsql = NewQuery
  FROM cte2
  ORDER BY rn DESC

  --PRINT @dsql
  EXEC sp_executesql @dsql

或使用COALESCE /
ISNULL函数进行字符串连接

DECLARE @xml xml =
    '<ROOT>
       <PARAMETROS>
        <USU_LOGIN>yleon</USU_LOGIN>
        <USU_NOMBREPRIMERO>Yerusha</USU_NOMBREPRIMERO>
        <USU_APELLIDOPRIMERO>Leon</USU_APELLIDOPRIMERO>
        <USU_EMAIL>[e-mail]</USU_EMAIL>
        <USU_FECHACREACION>20130510</USU_FECHACREACION>
        <USU_CODICIONES1 TIPO="MC">AND USU_ID=4</USU_CODICIONES1>
        <USU_CODICIONES2 TIPO="MC">AND USU_ID=5</USU_CODICIONES2>
        <USU_CODICIONES3 TIPO="HG">AND USU_ID=9</USU_CODICIONES3>
        <USU_CODICIONESN TIPO="MC">AND USU_ID=50</USU_CODICIONESN>
      </PARAMETROS>
    </ROOT>'

DECLARE @query nvarchar(max) = 
  'SELECT * FROM USUARIOS where 1=1 USU_CODICIONES1 USU_CODICIONES2 USU_CODICIONESn',
        @dsql nvarchar(max)

  SELECT @dsql = REPLACE(COALESCE(@dsql, @query), 
                         T.Item.value('fn:local-name(.)', 'varchar(255)'),
                         T.Item.value('data(.)', 'varchar(255)'))         
  FROM   @xml.nodes('/ROOT/PARAMETROS/*') AS T(Item)  
  WHERE T.Item.value('data(@TIPO)', 'varchar(255)')='MC'
  --PRINT @dsql
  EXEC sp_executesql @dsql