Generate XML - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Generate XML

Author

Topic

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2010-07-28 : 10:11:40

Hi I am not able to find SQL-XML segment here so i am posting it there..
i have a table in which i am having data like...

ID Value
1 a
1 b
1 c
1 d
like this... and i like to have XML like...
<ID No="1" />
<values>
<value ID="a" />
<value ID="b" />
<value ID="c" />
</values>

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-28 : 10:21:13

Where is Value "d"?

N 56°04'39.26"
E 12°55'05.63"

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2010-07-28 : 10:28:14

quote:
Originally posted by Peso

Where is Value "d"?

N 56°04'39.26"
E 12°55'05.63"

Thanks for the fast reply yes you can include that...i forgot to include that..this is typing mistake...final XML should be like this..

<ID No="1" />
<values>
<value ID="a" />
<value ID="b" />
<value ID="c" />
<value ID="d" />
</values>

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-28 : 10:40:18

[code]DECLARE @Sample TABLE
(
ID TINYINT NOT NULL,
Value CHAR(1) NOT NULL
)

INSERT @Sample
(
ID,
Value
)
SELECT 1, 'a' UNION ALL
SELECT 1, 'b' UNION ALL
SELECT 1, 'c' UNION ALL
SELECT 1, 'd'

SELECT CAST(i.Data + j.Data AS XML)
FROM (
SELECT ID AS [@No]
FROM @Sample
GROUP BY ID
FOR XML PATH('ID')
) AS i(Data)
CROSS APPLY (
SELECT Value AS [@ID]
FROM @Sample
FOR XML PATH('value'),
ROOT('values')
) AS j(Data)[/code]

N 56°04'39.26"
E 12°55'05.63"

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2010-07-28 : 10:43:59

hey i got it thanks....
i cracked it using XML Explicit..

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-28 : 10:48:31

You're welcome.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources