How to have an element and an attribute value in a tag

Question

How to have an element and an attribute value in a tag

Brent Seeney-273128

SSC Rookie

Points: 30
More actions
December 14, 2011 at 3:04 pm

#73042

I need an XML element that has an attribute and an element value. The result required is such:
<Party>
<CustomerID Role="SENDER">1122334455</CustomerID>
<Name>SomeOne</Name>
<Address1>SomeWhere</ADDRESS>
<City>SomeCity</City>
</Party>
This is a subquery in part of a larger SQL XML query, the data is static so no table is referenced. The DB is SQL Server 2005.
The query I am using is:
(
SELECT
'SENDER''CustomerID/@Role',
'1122334455''CustomerID',
'SomeOne''Name',
'SomeWhere''Address1',
'SomeCity''City'
) FOR XML path ('Party')

But the result is:
<Party>
<CustomerID Role="SENDER">
</CustomerID>
<Name>SomeOne</Name>
<Address1>SomeWhere</Address1>
<City>SomeCity</City>
</Party>
Any ideas on what is missing or wrong with my query?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Brent Seeney-273128 (12/14/2011)
I need an XML element that has an attribute and an element value. The result required is such:
<Party>
<CustomerID Role="SENDER">1122334455</CustomerID>
<Name>SomeOne</Name>
<Address1>SomeWhere</ADDRESS>
<City>SomeCity</City>
</Party>
This is a subquery in part of a larger SQL XML query, the data is static so no table is referenced. The DB is SQL Server 2005.
The query I am using is:
(
SELECT
'SENDER''CustomerID/@Role',
'1122334455''CustomerID',
'SomeOne''Name',
'SomeWhere''Address1',
'SomeCity''City'
) FOR XML path ('Party')

But the result is:
<Party>
<CustomerID Role="SENDER">
</CustomerID>
<Name>SomeOne</Name>
<Address1>SomeWhere</Address1>
<City>SomeCity</City>
</Party>
Any ideas on what is missing or wrong with my query?

When running your example, I'm getting exactly the result you're looking for (see attached screenshot).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

I get the expected result as well. What version of SQL Server are you running this on? I ran it on SQL Server 2008 R2 (RTM)

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Brent Seeney-273128 SSC Rookie Points: 30 More actions · Answer 3

Hi Guys

I was using SQL Manager from EMS rather than SQL Server Management Studio, and it turns out that they give different outputs.

I had been so confounded by this problem because it should have worked!

I will keep in mind to check everything with SQL Management Studio in future.

Thanks very much for your help 🙂

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 4

Brent Seeney-273128 (12/20/2011)
Hi Guys
I was using SQL Manager from EMS rather than SQL Server Management Studio, and it turns out that they give different outputs.
I had been so confounded by this problem because it should have worked!
I will keep in mind to check everything with SQL Management Studio in future.
Thanks very much for your help 🙂

Considering the wrong answers you just got from it, is there some significant advantage to using SQL Manager from EMS over SSMS?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)