Dynamic query construction looping conditions from

问题:

I need to construct a part of the conditions of my sql query from a xml.

I have a XML like:

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

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

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

The Expected result is:

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

回答1:

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

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