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
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
This helps you
SELECT TOP 1
'someValue' AS '@Attribute',
'text'
FOR XML PATH('YourElement'), ROOT('Root')
select 'hello' as [@attr1]
, 'world'
for xml path('mynode')
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')
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('')
