来自XML的动态查询构造循环条件？ [英] Dynamic query construction looping conditions from a XML?

问题描述

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

I need to construct a part of the conditions of my sql query from a 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>

所以我需要提取属性TIPO = MC的条件；我使用以下代码进行操作：

So I need to extract the conditions with the attribute TIPO="MC"; I do with this code:

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'

现在，假设我的查询是：

Now, suppose my query is:

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

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

I need to replace the '@@USU_CODICIONESX' looping the XML but not using a cursor, maybe a cte.

预期结果为：

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

推荐答案

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

You need to dynamic create a SQL statement and then run that command

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函数

Or String Concatenation using COALESCE/ISNULL function

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

这篇关于来自XML的动态查询构造循环条件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！