CODEWITHSUNDEEP
sql-serverxmlsql-server-2008-r2
Jānis
Jānis

Reputation: 2266

creating XML elements and values from table data

I have a table that stores data:

declare @t table
    (
        el nvarchar(50), 
        val nvarchar(50)
    )
insert into @t values ('n1', 'value 1'), ('n2', 'value 2'), ('n3', 'value 3 <>')

I should get xml, that look like this:

<data>
  <n1>value 1</n1>
  <n2>value 2</n2>
  <n3>value 3 &lt;&gt;</n3>
</data>

the best I could get so far is:

select cast('<' + el + '>' +  (select val from @t i where i.el = t.el for xml raw(''), elements)  + '</' + el + '>'  as xml)
from @t t
For XML Raw(''), root('data'), Elements, Type

Could you help? Thanks!

Upvotes: 2

Views: 53

Answers (3)

user1324819
user1324819

Reputation:

If you are using SQL Server 2012 you may try XML PATH

    SELECT TOP 1000 FirstName, LastName
  FROM [AdventureWorks2012].[Person].[Person]
  where FirstName like 'Ken%'
  and BusinessEntityID like '1514%'
  FOR XML PATH

Output will be

<row>
  <FirstName>Kendra</FirstName>
  <LastName>Alonso</LastName>
</row>
<row>
  <FirstName>Kendra</FirstName>
  <LastName>Moreno</LastName>
</row>
<row>
  <FirstName>Kendra</FirstName>
  <LastName>Munoz</LastName>
</row>
<row>
  <FirstName>Kendra</FirstName>
  <LastName>Romero</LastName>
</row>

For more examples, please refer http://msdn.microsoft.com/en-us/library/bb510462.aspx

Upvotes: 0

Andomar
Andomar

Reputation: 238086

You could convert the val attribute to XML:

cast((select val for xml path(''), type) as nvarchar(max))

That will give you <val>value 3 &lt;&gt;</val>, but you can strip off the <val> tags with replace:

select  cast('<' + el + '>' + 
        replace(replace(
            cast((select val for xml path(''), type) as nvarchar(max)), 
            '<val>', ''), '</val>', '') +
        '</' + el + '>' as xml)
from    @t
for     xml path(''), root('data')

Upvotes: 2

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

There is no need to join on el and you can use * to remove the tag name val.

select cast('<'+el+'>'+(select val as '*' for xml path(''))+'</'+el+'>' as xml)
from @T
for xml path(''), root('data')

Result:

<data>
  <n1>value 1</n1>
  <n2>value 2</n2>
  <n3>value 3 &lt;&gt;</n3>
</data>

Upvotes: 2

Related Questions