SQL Server for xml path add attributes and values

Question

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

Solution 1

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

---

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

Solution 2

This helps you

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

Solution 3

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

Comments

[Keith John Hutchison]

interesting ... I imagine this would kill/stop a server if the database was sufficiently large.

[hkravitz]

This is effective for small databases up to 10GB of data, also depends on your business needs. I used it when I needed to import a database to a third party which specifically requested a XML format in order to incorporate the data with their Big data solution as another source.

[hkravitz]

It took 15-20 minutes