Create another nested XML hierarchy

<< Easy script for calculating weekday or weekend for a date | Home | Alternative approach to calculate most used time interval >>

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

Feedback

# 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

# 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

# re: Create another nested XML hierarchy

Excellent ... i need the same

9/21/2012 4:23 PM | purushothama reddy

# 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.

Thinking outside the box

My Links

Advertisement

News

Archives

Post Categories