Create another nested XML hierarchy
-- Prepare sample data
DECLARE @Sample TABLE
(
GalaxyID INT,
ObjectID VARCHAR(16),
ObjectType VARCHAR(5),
ObjectTitle VARCHAR(200)
)
INSERT @Sample
SELECT 1, 'T022520001611242', 'Topic', 'Business Strategy' UNION ALL
SELECT 1, 'T021320001145243', 'Topic', 'Decision Making' UNION ALL
SELECT 1, 'T8150310322032', 'Topic', 'New Growth' UNION ALL
SELECT 1, 'T97200019493829', 'Topic', 'Marketing Strategy' UNION ALL
SELECT 2, 'T1210018575047', 'Topic', 'Strategic Relationships' UNION ALL
SELECT 2, 'T1027001655860', 'Topic', 'Globalization' UNION ALL
SELECT 2, 'T95200015582307', 'Topic', 'Strategic Sourcing' UNION ALL
SELECT 2, 'T021120001714561', 'Topic', 'Business Processes & Architectures' UNION ALL
SELECT 3, 'T011820001527219', 'Topic', 'Business Models' UNION ALL
SELECT 3, 'T022520001622334', 'Topic', 'Venture Capital Processes' UNION ALL
SELECT 3, 'T524200010114538', 'Topic', 'Entrepreneurial Thinking' UNION ALL
SELECT 3, 'T011820001524538', 'Topic', 'Drivers of Change & Growth'
-- Display the XML
SELECT s.GalaxyID AS [@Id],
(
SELECT x.ObjectID AS [@Id],
x.ObjectType AS [@Type],
x.ObjectTitle AS [Title]
FROM @Sample AS x
WHERE x.GalaxyID = s.GalaxyID
FOR XML PATH('Object'),
TYPE
)
FROM (
SELECT GalaxyID
FROM @Sample
GROUP BY GalaxyID
) AS s
FOR XML PATH('Galaxy'),
ROOT('Universe')
DECLARE @Sample TABLE
(
GalaxyID INT,
ObjectID VARCHAR(16),
ObjectType VARCHAR(5),
ObjectTitle VARCHAR(200)
)
INSERT @Sample
SELECT 1, 'T022520001611242', 'Topic', 'Business Strategy' UNION ALL
SELECT 1, 'T021320001145243', 'Topic', 'Decision Making' UNION ALL
SELECT 1, 'T8150310322032', 'Topic', 'New Growth' UNION ALL
SELECT 1, 'T97200019493829', 'Topic', 'Marketing Strategy' UNION ALL
SELECT 2, 'T1210018575047', 'Topic', 'Strategic Relationships' UNION ALL
SELECT 2, 'T1027001655860', 'Topic', 'Globalization' UNION ALL
SELECT 2, 'T95200015582307', 'Topic', 'Strategic Sourcing' UNION ALL
SELECT 2, 'T021120001714561', 'Topic', 'Business Processes & Architectures' UNION ALL
SELECT 3, 'T011820001527219', 'Topic', 'Business Models' UNION ALL
SELECT 3, 'T022520001622334', 'Topic', 'Venture Capital Processes' UNION ALL
SELECT 3, 'T524200010114538', 'Topic', 'Entrepreneurial Thinking' UNION ALL
SELECT 3, 'T011820001524538', 'Topic', 'Drivers of Change & Growth'
-- Display the XML
SELECT s.GalaxyID AS [@Id],
(
SELECT x.ObjectID AS [@Id],
x.ObjectType AS [@Type],
x.ObjectTitle AS [Title]
FROM @Sample AS x
WHERE x.GalaxyID = s.GalaxyID
FOR XML PATH('Object'),
TYPE
)
FROM (
SELECT GalaxyID
FROM @Sample
GROUP BY GalaxyID
) AS s
FOR XML PATH('Galaxy'),
ROOT('Universe')
Legacy Comments
|
twh
2011-03-17 |
re: Create another nested XML hierarchy Thank you so much for posting this. It's exactly what I needed, |
|
Bob Johnson
2011-03-17 |
re: Create another nested XML hierarchy I can't thank you enough. I was trying to pound in a nail with sledge hammer. Your example is way easier than using the FLWOR and XML Query command. |
|
purushothama reddy
2012-09-21 |
re: Create another nested XML hierarchy Excellent ... i need the same |
|
purushothama reddy
2012-09-21 |
re: Create another nested XML hierarchy <?xml version="1.0" encoding="UTF-8" ?> - <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:agen="stgmastek.com/.../" xmlns:lib="stgmastek.com/.../library"> <soapenv:Header /> <soapenv:Body> </soapenv:Body> </soapenv:Envelope> i need to generate above xml string i wrote the code BELLOW select '' Header, ( select '' asas FOR XML PATH('Body'), type ) FOR XML PATH('SOAPENV:Envolop') with this i am getting error like XML name space prefix 'SOAPENV' declaration is missing for FOR XML row name 'SOAPENV:Envolop'. can anyone help me.. ? |