Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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')

Print | posted on Tuesday, January 20, 2009 9:50 PM | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

Feedback

Gravatar

# re: Create another nested XML hierarchy

Thank you so much for posting this. It's exactly what I needed,
3/17/2011 3:26 PM | twh
Gravatar

# 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.
3/17/2011 9:14 PM | Bob Johnson
Gravatar

# re: Create another nested XML hierarchy

Excellent ... i need the same
9/21/2012 4:23 PM | purushothama reddy
Gravatar

# 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.. ?
9/21/2012 4:30 PM | purushothama reddy
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET