XML problem: Mixing attributes and data

,
1

Using this query:

With cte(answer, computer) as 
(select 42, 'Deep Thought') 

select answer as '@answer', computer from cte
for xml path('Question')

I get:

<Question answer="42"><computer>Deep Thought</computer></Question>

but I want:

<Question answer="42">Deep Thought</Question>

How should I change my query to get that?

2

Got one solution:

With cte(answer, computer) as 
(select 42, 'Deep Thought') 

select answer as 'Question/@answer', computer AS 'Question' from cte
for xml path('')

Hoping for something simpler (so I don't have to repeat 'Question' for each attribute)

3

not sure what your actual (presumably complex) code is, and thus this is probably no help, or you've already considered it

With cte([Question/@answer], [Question]) as 
(select 42, 'Deep Thought') 

select cte.* from cte
for xml path('')

Doesn't help with "so I don't have to repeat 'Question' for each attribute" unless moving it to the CTE definition makes whatever you are attempting more easily coded.

4

Yup! The actual code has many attributes and one piece of text. So whether I do it your way or mine, I still wind up repeating ''Question" many times. That's what I'm looking to avoid.

Just to keep things DRY, you see!