question

emil87b avatar image
emil87b asked

FOR XML STRUGGLE

Hi, I have an example data set: CREATE TABLE Trans( Section nvarchar(255) NULL, Item nvarchar(255) NULL, Translation nvarchar(255) NULL ); INSERT INTO Trans VALUES ('language','en-GB',N'영어'); INSERT INTO Trans VALUES ('language','el-GR',N'그리스어'); INSERT INTO Trans VALUES ('language','fr-Fr',N'프랑스어'); INSERT INTO Trans VALUES ('language','ru-RU',N'러시아어'); INSERT INTO Trans VALUES ('generic','logout',N'[E]로그아웃'); INSERT INTO Trans VALUES ('generic','ok',N'[E]확인'); INSERT INTO Trans VALUES ('generic','cancel',N'[E]취소'); INSERT INTO Trans VALUES ('generic','continue',N'[E]계속'); INSERT INTO Trans VALUES ('generic','CheckBox',N'[E]체크박스'); INSERT INTO Trans VALUES ('generic','close',N'[E]닫기'); INSERT INTO Trans VALUES ('generic','translations',N'[E]번역'); INSERT INTO Trans VALUES ('generic','modify',N'수정'); INSERT INTO Trans VALUES ('generic','exit',N'[E]나가기'); INSERT INTO Trans VALUES ('generic','results',N'[E]결과'); INSERT INTO Trans VALUES ('generic','menu',N'[E]메뉴'); and would like to get an xml in format: any advices how to approach it as i didn't have much to do before with creating xml's in SQL. The closest i can get is SELECT Section "section/@name" , Item "section/item-list/item/@name" , Translation AS "section/item-list/item/@value" FROM dbo.Trans FOR XML PATH(''), ROOT ('section-list') just need to group it some how by section name
queryxml
2 comments
10 |1200

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

KenJ avatar image KenJ commented ·
Turns out that "for xml" and "struggle" are synonymous
3 Likes 3 ·
emil87b avatar image emil87b commented ·
Yes :) totally agree
0 Likes 0 ·

1 Answer

·
emil87b avatar image
emil87b answered
ok i think i found solution, may not be the best one but looks how it supposed to: SELECT Section [@section], (SELECT T.Item [@name], T.Translation [@value] FROM Trans T WHERE T.Section = S.Section FOR XML PATH('item'), TYPE) AS [item-list] FROM (SELECT DISTINCT Section FROM Trans) S FOR XML PATH ('section'), ROOT('section-list') any observations?
1 comment
10 |1200

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

KenJ avatar image KenJ commented ·
That looks good. Just change `SELECT Section [@section]` to `SELECT Section [@name]` and you'll have a match to the desired output.
1 Like 1 ·

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.