CODEWITHSUNDEEP
sql-serverxmlxpath
tuxmania
tuxmania

Reputation: 956

SQL Server for xml path add attributes and values

I want to create an xml node that contains attributes as well as values.

Example:

<mynode attr1="hello">world</mynode>

I understand that

SELECT 'hello' as '@attr1'

creates at least mynode, but how to attach "world" then?

I tried

SELECT 'world' as 'mynode/[@attr1=hello]'

but it didn't work

Upvotes: 12

Views: 28017

Answers (5)

bookofproofs
bookofproofs

Reputation: 354

The previous answer

select 'hello' as [@attr1], 'world' for xml path('mynode')

only works because 'world' is a constant one-row result. It will stop working as you wish if you need multiple rows like in

select 'hello' as [@attr1], world 
from 
   (select 'world1' AS world 
    union 
    select 'world2') temp
for xml path('mynode')

because it will give you the unwanted result

<mynode attr1="hello">
  <world>world1</world>
</mynode>
<mynode attr1="hello">
  <world>world2</world>
</mynode>

So, if you are after the result

<mynode attr1="hello">world1</mynode>
<mynode attr1="hello">world2</mynode>

you have to use

select 'hello' as [mynode/@attr1], world as mynode
   (select 'world1' AS world 
    union 
    select 'world2') temp
for xml path('')

Upvotes: 0

hkravitz
hkravitz

Reputation: 1385

The following script enables you to extract all your data from all tables in your database into a single XML file:

SET NOCOUNT ON 
DECLARE @CMD varchar(max) = ''

DECLARE @AllTablesXML table (XMLData XML)
SELECT @CMD += 
  ';SELECT '''+TABLE_NAME+''' as ''@name'' ,
    (
    SELECT * 
    FROM ' + QUOTENAME(T.TABLE_SCHEMA) + '.' + QUOTENAME(T.TABLE_NAME) +
    'FOR XML PATH (''Columns'') ,type ' + CHAR(10) + '
    )
   FOR XML PATH (''Table''),  type'
   FROM INFORMATION_SCHEMA.TABLES T
  --WHERE T.TABLE_NAME in  ('your List of tables') Uncomment if you need to extract specific tables

INSERT INTO @AllTablesXML
EXEC (@CMD) 


SELECT XMLData 
FROM @AllTablesXML
FOR XML PATH (''), ROOT('Alltables')

Upvotes: 0

blogbydev
blogbydev

Reputation: 1485

select 'hello' as [@attr1]
, 'world'
for xml path('mynode')

Upvotes: 3

Orlando Herrera
Orlando Herrera

Reputation: 3531

This is a basic example:

SELECT ( SELECT 'White' AS Color1,
'Blue' AS Color2,
'Black' AS Color3,
'Light' AS 'Color4/@Special',
'Green' AS Color4,
'Red' AS Color5
 FOR
 XML PATH('Colors'),
 TYPE
 ),
 ( SELECT 'Apple' AS Fruits1,
 'Pineapple' AS Fruits2,
 'Grapes' AS Fruits3,
 'Melon' AS Fruits4
 FOR
 XML PATH('Fruits'),
 TYPE
 )
 FOR XML PATH(''),
 ROOT('SampleXML')

How to create XML data in SQL SERVER

Please, for more information you can visit this page SQL SERVER – Simple Example of Creating XML File Using T-SQL

Upvotes: 14

okanguz
okanguz

Reputation: 61

This helps you

SELECT TOP 1 
   'someValue' AS '@Attribute',
   'text' 
FOR XML PATH('YourElement'), ROOT('Root')

Upvotes: 6

Related Questions