output XML with combiantion of attribute & element
-
I have one table
declare @temp table (col1 varchar(300), col2 varchar(300))INSERT INTO @temp
VALues('Measure ', '10.6227')
iNSERT INTO @temp
VALUES('Verify ', '02 1F 01 02 00 DB')
Select * from @temp
and I need to output xml in this format:
< D N="Measure">
10.6227< /D>
<D N="Verify">
02 1F 01 02 00 DB
< /D>
but using this,
SELECT col1 as N,
( SELECT col2
FROM @temp A
WHERE A.col1 = B.col1
FOR XML AUTO, TYPE)FROM @temp B
FOR XML RAW('D'), TYPE
I get:
< D N="Measure">
<A col2="10.6227" />
< /D>
<D N="Verify">
<A col2="02 1F 01 02 00 DB" />
< /D>
-
SELECT col1 AS "@N",col2 AS "text()"
FROM @temp
FOR XML PATH('D'),ROOT('Root'),TYPE
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Mark (7/9/2008)
SELECT col1 AS "@N",col2 AS "text()"
FROM @temp
FOR XML PATH('D'),ROOT('Root'),TYPE
I cann't , I get this :
< D>
< N>Measure </N>10.6227 </D>
< D>
< N>Verify </N>02 1F 01 02 00 DB </D>
with:
SELECT col1 AS N,
col2 AS 'text()'
FROM @temp
FOR XML PATH('D') ,TYPE
(i no need root tag)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply