CODEWITHSUNDEEP
sql-serverxmlsql-server-2008sql-server-2005xquery
ca9163d9
ca9163d9

Reputation: 29159

How to convert xml to non-xml text using XQuery in SQL Server 2005/2008?

The following code will return select $f/field/text() from table1

declare @x xml = '<meta><field>Column1</field></meta>';
select @x.query('
for $f in /meta
return
    "select $f/field/text() from table1"
')

I tried to change the return part to "select"+$f/field or "select" $f/field and these cannot be parsed.

Update:
The @x will be generated from a table with for xml raw,elements. The purpose to use xml/xquery is to avoid the boilerplate string concatenations. It seems XQuery cannot transform xml to text?

Updated question:
The following is the code I want to write.

declare @meta table (field sysname primary key, validate varchar(8000));
insert into @meta values ('Col1', '< 100'), ('Col2', '> Col1');

declare @x xml = (select * from @meta for xml path('meta'));
select @x;

-- The following code cannot be parsed. It will be nice to replace /meta/field/text() with shorter variable too
select @x.query('
    "select 1" 
        {"," /meta/field/text() "= case when" /meta/field/text() /meta/validate/text() "then" /meta/field/text()}
    "from table1"
    "where 1=1 " 
        { "or (" /meta/field/text() /meta/validate/text() ")" }
')

And the generated result should be,

select 1
    , Col1 = case when Col1 < 100 then Col1 end
    , Col2 = case when Col2 > Col1 then Col2 end
from table1
where 1=1
        or (Col1 < 100)
        or (Col2 > Col1)

Upvotes: 1

Views: 876

Answers (3)

ca9163d9
ca9163d9

Reputation: 29159

Just need to wrap everything in an outer xml tag.

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Use .value instead of .query to get a value from XML.

declare @x xml = '<meta><field>Column1</field></meta>';

select 'select '+quotename(@x.value('(/meta/field)[1]', 'sysname'))+' from table1'

Result:

select [Column1] from table1

Update:

Here is a way to build your query using the meta table directly.

select 'select '+
       stuff((select ', '+field+' = case when '+field+' '+validate+' then '+field+' end'
              from @meta 
              for xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, '')+
       ' from table1 where '+
       stuff((select ' or ('+field+' '+validate+')'
              from @meta 
              for xml path(''), type).value('.', 'nvarchar(max)'), 1, 4, '')

Result:

select Col1 = case when Col1 < 100 then Col1 end,
       Col2 = case when Col2 > Col1 then Col2 end 
from table1 
where (Col1 < 100) or 
      (Col2 > Col1)

Upvotes: 2

Harold Sota
Harold Sota

Reputation: 7566

The solution:

--drop table #tmp
declare @x xml = '<meta><field>Column1</field> <field>Column2</field></meta>';
declare @field  varchar(max) 
CREATE TABLE #tmp (field varchar(max) )

set @field = ( select cast(@x.query('for $f in /meta/field
return  concat("insert into #tmp(field)  select ", $f, " from table1;")
') as varchar(max)))

 EXEC (@field);

 select field from #tmp

 drop table #tmp

Upvotes: 1

Related Questions