question

sanket avatar image
sanket asked

Convert xml to JSON

How to convert nested xml string to JSON in sql server 2008 ? for e.g. my xml string is like 11 B000111 980977854 008773033
sql-server-2008tsqlxml
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tomgough79 avatar image tomgough79 commented ·
Whilst you could use XQuery to extract the data then manipulate into JSON (I'm not a JSON expert so not sure how easy that is), it might be easier to process client side. Maybe write something in C#?
0 Likes 0 ·
sanket avatar image sanket commented ·
My requirement is from sql query (e.g. select * from table1) o/p should be JSON format. Their can be case like query can take data from join of multiple tables.
0 Likes 0 ·
sanket avatar image sanket commented ·
Is there any way with which I can change above mentioned nested xml to JSON in sql server ?
0 Likes 0 ·

1 Answer

·
Kev Riley avatar image
Kev Riley answered
SQL Server doesn't handle JSON natively, so I would suggest reading Phil Factor's articles on JSON on Simple Talk : https://www.simple-talk.com/blogs/2013/03/26/sql-server-json-to-table-and-table-to-json/
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.