How do I alter the output where the attributes are elements in this MS SQL statement

Jack Seaman
Jack Seaman used Ask the Experts™
on
Here is the statement I am using to generate the XML.  However it need to have attributes that are elements/


select id, Rtrim(title)as title1, author, datecreated, Rtrim(body) as body1, image1,
REPLACE(RTRIM(SUBSTRING(Title, 1, 25)), ' ', '-') AS pagename
from ecsuheadlines
FOR XML AUTO

Current output:
<ecsuheadlines id="428" title1="Faculty make headlines" author="Kesha Williams" datecreated="2005-01-03T00:00:00" body1="ECSU announces the following briefs of achievements by its faculty and staff: &lt;p&gt;&#xD;&#xA;&#xD;&#xA; &#xD;&#xA;&#xD;&#xA;Dr. Carolyn R. Mahoney, ECSU Provost and Vice Chancellor for Academic Affairs, was a co-author on the National Research Council Report &quot;On Evaluating Curricular Effectiveness: Judging the Quality of K-12 Mathematics Evaluations,&quot; Washington, DC, at the National Academies Press in May 2004.  &lt;p&gt;&#xD;&#xA;&#xD;&#xA;Dr. Carolyn Mahoney, ECSU Provost and Vice Chancellor of Academic Affairs, was an invited plenary speaker at the Education Trust annual national conference on closing the achievement gap. Her talk, entitled &quot;Access, Retention and Completion: The ECSU Story&quot; was delivered to national educators, community leaders and policy makers in Washington, DC in November


Preferred output
<headlines>
<headline>
                <id>428</id>
                <title1> Faculty make headlines </title1>
                <author> Kesha Williams </author>
                <datecreated>2005-01-03T00:00:00</datecreated>
                <body1>ECSU announces the following briefs of achievements by its faculty and staff: &lt;p&gt;&#xD;&#xA;&#xD;&#xA; &#xD;&#xA;&#xD;&#xA;Dr. Carolyn R. Mahoney, ECSU Provost and Vice Chancellor for Academic Affairs, was a co-author on the National Research Council Report &quot;On Evaluating Curricular Effectiveness: Judging the Quality of K-12 Mathematics Evaluations,&quot; Washington, DC, at the National Academies Press in May 2004.  &lt;p&gt;&#xD;&#xA;&#xD;&#xA;Dr. Carolyn Mahoney, ECSU Provost and Vice Chancellor of Academic Affairs, was an invited plenary speaker at the Education Trust annual national conference on closing the achievement gap. Her talk, entitled &quot;Access, Retention and Completion: The ECSU Story&quot; was delivered to national educators, community leaders and policy makers in Washington, DC in November
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
Will this assist?
in your query:
changing
FOR XML AUTO
to
FOR XML RAW, ELEMENTS
would produce an xml structure similar to this
<row>
    <id>428</id>
    <title>Faculty make headlines</title>
    <author>Kesha William</author>
    <datecreated>2005-01-03T00:00:00</datecreated>
    <body>ECSU announces the following briefs of achievements by its faculty and staff</body>
    <pagename>Faculty-make-headlines ECSU announces the following briefs of achievements by its faculty and staff</pagename>
</row>

Open in new window


test case:
**MS SQL Server 2008 Schema Setup**:

    CREATE TABLE ecsuheadlines 
        ([id] int, [title1] varchar(80), [author] varchar(80), [datecreated] datetime, [body1] varchar(800))
    ;
        
    INSERT INTO ecsuheadlines 
        ([id], [title1], [author], [datecreated], [body1])
    VALUES
        (428, 'Faculty make headlines', 'Kesha William', '2005-01-03 00:00:00', 'ECSU announces the following briefs of achievements by its faculty and staff')
    ;
    
**Query 1**:

    SELECT
          id
        , RTRIM(title1) AS title1
        , author
        , datecreated
        , RTRIM(body1) AS body1
        --, image1
        , REPLACE(RTRIM(SUBSTRING(Title1, 1, 25)), ' ', '-') AS pagename
    FROM ecsuheadlines
    FOR XML RAW, ELEMENTS
    

**[Results][2]**:
    |                                                                                                                                                                                                                                         XML_F52E2B61-18A1-11d1-B105-00805F49916B |
    |----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
    | <row><id>428</id><title1>Faculty make headlines</title1><author>Kesha William</author><datecreated>2005-01-03T00:00:00</datecreated><body1>ECSU announces the following briefs of achievements by its faculty and staff</body1><pagename>Faculty-make-headlines</pagename></row> |

  [1]: http://sqlfiddle.com/#!3/8cd57/10

Open in new window

Author

Commented:
I've tried that and it seems to work.  I've set off the file and will see if this is expectable.  Should know by EOB today.

Author

Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for wjseaman's comment #a40946175

for the following reason:

It worked.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
If my solution worked, why is this being closed with zero points?

Author

Commented:
Where do I award points?
Not that I didn't want too....

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial