January 6, 2014 at 1:52 am
Hi all,
I have table with following structure:
Create table Table1 (ID int identity(1,1),Locale nvarchar(10), Key nvarchar(40), Value nvarchar(40))
INSERT INTO table1 (Locale, Key, Value)
VALUES ('én_US', 'btnAdd', 'Add')
How can I create XML in below given format:
<tbl>
<Array>
<Nil />
</Array>
<Hash>
<HashCell>
<Str value="btnAdd" />
<Str value="Add" />
</HashCell>
</Hash>
</tbl>
I tried with this query so far:
SELECT ,[value]
FROM table1 as HashCell
FOR XML AUTO, ELEMENTS, ROOT('hash')
Thanks in advnace
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
January 6, 2014 at 8:30 am
Your table has an issue in that you are attempting to put both columns in using the same element and value, which SQLXML is not going to like.
That said you can get most of the way with this query:
select (Select null as "junk" for XML PATH('Nil'),root('Array'),type)
,(
SELECT ,[value] as "str/@value"
FROM #table1 as HashCell
FOR XML path('HashCell'), ELEMENTS, ROOT('Hash'),type)
for XML path('tbl')
You really should steer clear of FOR XML AUTO if you can avoid it. You will get much farther if you use the PATH version.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 6, 2014 at 9:16 pm
Thanks Miller for your advice 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply