Does XML AUTO support what I am trying to do?

Question

Hi

At present we have a denormalised database. ie

Name|NameID|EmployeeID|EmployeeType

I'm normalising the database to resolve the EmployeeID from the Employee table rather than the Name Table.

So we have a select at the moment

SELECT Name, NameID, EmployeeID, EmployeeType FROM Name 
FOR XML AUTO

Which will output:

 <Name Name='Fred' NameID='1' EmployeeID='1' EmployeeType='Manager'>

Now I have an INNER JOIN

SELECT Name, NameID, Name.EmployeeID, Employee.EmployeeType FROM Name
INNER JOIN Employee ON Name.EmployeeID = Employee.EmployeeID
FOR XML AUTO

Now my XML looks like this:

<Name Name='Fred' NameID='1' EmployeeID='1'>
  <Employee EmployeeType='Manager' />
</Name>

My question is , is there any way to get XML AUTO to output my XML as before, or do i have to move to XML Explicit?

Answer 1

Can you use FOR XML PATH instead?? It gives you a lot more control over the layout of your XML, and it's A LOT EASIER than XML EXPLICIT! :-)

SELECT 
   Name as '@Name', 
   NameID as '@NameID', 
   Name.EmployeeID as '@EmployeeID', 
   Employee.EmployeeType as '@EmployeeType'
FROM Name
INNER JOIN Employee ON Name.EmployeeID = Employee.EmployeeID
FOR XML PATH('Name')

The PATH('Name') defines that the whole element should be a <Name> element, and if you use alias for your columns with a leading @ then you'll get attributes (without the leading "@", you get subelements).

Check out the MSDN article on What's new in SQL Server 2005 XML for some more details and examples.

Marc