There are a few cool ways you can pull data out of SQL Server 2005/8 as Xml. That is, it will be returned from your tables and converted to Xml on the fly.
For the purposes of this post, I will be querying the Department table of the AdventureWorks2008 database.
First Query:
SELECT *
FROM HumanResources.Department
FOR XML RAW
This returns a collection of “row” elements, with each of the fields from your table included as attributes of those elements:
<row DepartmentID="1" Name="Engineering" GroupName="Research and Development" ModifiedDate="1998-06-01T00:00:00" />
<row DepartmentID="2" Name="Tool Design" GroupName="Research and Development" ModifiedDate="1998-06-01T00:00:00" />
<row DepartmentID="3" Name="Sales" GroupName="Sales and Marketing" ModifiedDate="1998-06-01T00:00:00" />
<row DepartmentID="4" Name="Marketing" GroupName="Sales and Marketing" ModifiedDate="1998-06-01T00:00:00" />
<row DepartmentID="5" Name="Purchasing" GroupName="Inventory Management" ModifiedDate="1998-06-01T00:00:00" />
<row DepartmentID="6" Name="Research and Development" GroupName="Research and Development" ModifiedDate="1998-06-01T00:00:00" />
<row DepartmentID="7" Name="Production" GroupName="Manufacturing" ModifiedDate="1998-06-01T00:00:00" />
<row DepartmentID="8" Name="Production Control" GroupName="Manufacturing" ModifiedDate="1998-06-01T00:00:00" />
<row DepartmentID="9" Name="Human Resources" GroupName="Executive General and Administration" ModifiedDate="1998-06-01T00:00:00" />
<row DepartmentID="10" Name="Finance" GroupName="Executive General and Administration" ModifiedDate="1998-06-01T00:00:00" />
<row DepartmentID="11" Name="Information Services" GroupName="Executive General and Administration" ModifiedDate="1998-06-01T00:00:00" />
<row DepartmentID="12" Name="Document Control" GroupName="Quality Assurance" ModifiedDate="1998-06-01T00:00:00" />
<row DepartmentID="13" Name="Quality Assurance" GroupName="Quality Assurance" ModifiedDate="1998-06-01T00:00:00" />
<row DepartmentID="14" Name="Facilities and Maintenance" GroupName="Executive General and Administration" ModifiedDate="1998-06-01T00:00:00" />
<row DepartmentID="15" Name="Shipping and Receiving" GroupName="Inventory Management" ModifiedDate="1998-06-01T00:00:00" />
<row DepartmentID="16" Name="Executive" GroupName="Executive General and Administration" ModifiedDate="1998-06-01T00:00:00" />
Second Query:
SELECT *
FROM HumanResources.Department
FOR XML AUTO
This returns a collection of “Department” elements. Again, each of the fields from the table is included as attributes of those elements. But the name of the elements are now a little more descriptive than just “row”:
<HumanResources.Department DepartmentID="1" Name="Engineering" GroupName="Research and Development" ModifiedDate="1998-06-01T00:00:00" />
<HumanResources.Department DepartmentID="2" Name="Tool Design" GroupName="Research and Development" ModifiedDate="1998-06-01T00:00:00" />
<HumanResources.Department DepartmentID="3" Name="Sales" GroupName="Sales and Marketing" ModifiedDate="1998-06-01T00:00:00" />
<HumanResources.Department DepartmentID="4" Name="Marketing" GroupName="Sales and Marketing" ModifiedDate="1998-06-01T00:00:00" />
<HumanResources.Department DepartmentID="5" Name="Purchasing" GroupName="Inventory Management" ModifiedDate="1998-06-01T00:00:00" />
<HumanResources.Department DepartmentID="6" Name="Research and Development" GroupName="Research and Development" ModifiedDate="1998-06-01T00:00:00" />
<HumanResources.Department DepartmentID="7" Name="Production" GroupName="Manufacturing" ModifiedDate="1998-06-01T00:00:00" />
<HumanResources.Department DepartmentID="8" Name="Production Control" GroupName="Manufacturing" ModifiedDate="1998-06-01T00:00:00" />
<HumanResources.Department DepartmentID="9" Name="Human Resources" GroupName="Executive General and Administration" ModifiedDate="1998-06-01T00:00:00" />
<HumanResources.Department DepartmentID="10" Name="Finance" GroupName="Executive General and Administration" ModifiedDate="1998-06-01T00:00:00" />
<HumanResources.Department DepartmentID="11" Name="Information Services" GroupName="Executive General and Administration" ModifiedDate="1998-06-01T00:00:00" />
<HumanResources.Department DepartmentID="12" Name="Document Control" GroupName="Quality Assurance" ModifiedDate="1998-06-01T00:00:00" />
<HumanResources.Department DepartmentID="13" Name="Quality Assurance" GroupName="Quality Assurance" ModifiedDate="1998-06-01T00:00:00" />
<HumanResources.Department DepartmentID="14" Name="Facilities and Maintenance" GroupName="Executive General and Administration" ModifiedDate="1998-06-01T00:00:00" />
<HumanResources.Department DepartmentID="15" Name="Shipping and Receiving" GroupName="Inventory Management" ModifiedDate="1998-06-01T00:00:00" />
<HumanResources.Department DepartmentID="16" Name="Executive" GroupName="Executive General and Administration" ModifiedDate="1998-06-01T00:00:00" />
Third Query:
If we want the enclose the returned results in an element, and represent each field in the result set as an element, this is easily done:
SELECT *
FROM HumanResources.Department
FOR XML AUTO, ROOT('Departments'), ELEMENTS;
The return result now has a parent element Departments, and each field is an element:
<Departments>
<HumanResources.Department>
<DepartmentID>1</DepartmentID>
<Name>Engineering</Name>
<GroupName>Research and Development</GroupName>
<ModifiedDate>1998-06-01T00:00:00</ModifiedDate>
</HumanResources.Department>
<HumanResources.Department>
<DepartmentID>2</DepartmentID>
<Name>Tool Design</Name>
<GroupName>Research and Development</GroupName>
<ModifiedDate>1998-06-01T00:00:00</ModifiedDate>
</HumanResources.Department>
<HumanResources.Department>
<DepartmentID>3</DepartmentID>
<Name>Sales</Name>
<GroupName>Sales and Marketing</GroupName>
<ModifiedDate>1998-06-01T00:00:00</ModifiedDate>
</HumanResources.Department>
<HumanResources.Department>
<DepartmentID>4</DepartmentID>
<Name>Marketing</Name>
<GroupName>Sales and Marketing</GroupName>
<ModifiedDate>1998-06-01T00:00:00</ModifiedDate>
</HumanResources.Department>
<HumanResources.Department>
<DepartmentID>5</DepartmentID>
<Name>Purchasing</Name>
<GroupName>Inventory Management</GroupName>
<ModifiedDate>1998-06-01T00:00:00</ModifiedDate>
</HumanResources.Department>
<HumanResources.Department>
<DepartmentID>6</DepartmentID>
<Name>Research and Development</Name>
<GroupName>Research and Development</GroupName>
<ModifiedDate>1998-06-01T00:00:00</ModifiedDate>
</HumanResources.Department>
<HumanResources.Department>
<DepartmentID>7</DepartmentID>
<Name>Production</Name>
<GroupName>Manufacturing</GroupName>
<ModifiedDate>1998-06-01T00:00:00</ModifiedDate>
</HumanResources.Department>
<HumanResources.Department>
<DepartmentID>8</DepartmentID>
<Name>Production Control</Name>
<GroupName>Manufacturing</GroupName>
<ModifiedDate>1998-06-01T00:00:00</ModifiedDate>
</HumanResources.Department>
<HumanResources.Department>
<DepartmentID>9</DepartmentID>
<Name>Human Resources</Name>
<GroupName>Executive General and Administration</GroupName>
<ModifiedDate>1998-06-01T00:00:00</ModifiedDate>
</HumanResources.Department>
<HumanResources.Department>
<DepartmentID>10</DepartmentID>
<Name>Finance</Name>
<GroupName>Executive General and Administration</GroupName>
<ModifiedDate>1998-06-01T00:00:00</ModifiedDate>
</HumanResources.Department>
<HumanResources.Department>
<DepartmentID>11</DepartmentID>
<Name>Information Services</Name>
<GroupName>Executive General and Administration</GroupName>
<ModifiedDate>1998-06-01T00:00:00</ModifiedDate>
</HumanResources.Department>
<HumanResources.Department>
<DepartmentID>12</DepartmentID>
<Name>Document Control</Name>
<GroupName>Quality Assurance</GroupName>
<ModifiedDate>1998-06-01T00:00:00</ModifiedDate>
</HumanResources.Department>
<HumanResources.Department>
<DepartmentID>13</DepartmentID>
<Name>Quality Assurance</Name>
<GroupName>Quality Assurance</GroupName>
<ModifiedDate>1998-06-01T00:00:00</ModifiedDate>
</HumanResources.Department>
<HumanResources.Department>
<DepartmentID>14</DepartmentID>
<Name>Facilities and Maintenance</Name>
<GroupName>Executive General and Administration</GroupName>
<ModifiedDate>1998-06-01T00:00:00</ModifiedDate>
</HumanResources.Department>
<HumanResources.Department>
<DepartmentID>15</DepartmentID>
<Name>Shipping and Receiving</Name>
<GroupName>Inventory Management</GroupName>
<ModifiedDate>1998-06-01T00:00:00</ModifiedDate>
</HumanResources.Department>
<HumanResources.Department>
<DepartmentID>16</DepartmentID>
<Name>Executive</Name>
<GroupName>Executive General and Administration</GroupName>
<ModifiedDate>1998-06-01T00:00:00</ModifiedDate>
</HumanResources.Department>
</Departments>
If you want to keep the fields as attributes, just leave off the “, ELEMENTS;” in the last query. Or, if you just want a collection of elements without a parent, leave off the “ROOT(‘Departments’)” in that query.
So, those commands are pretty cool if you want to generate some xml data to play around with. Or, of course, if you need to in the course of your work!