Monday, July 20, 2009

Using XML in SQL server 2005

use Northwind

SELECT dbo.Customers.CustomerID, dbo.Customers.CompanyName, dbo.Orders.OrderID, dbo.Orders.OrderDate, dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity FROM dbo.Orders INNER JOIN dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID INNER JOIN dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID FOR xml raw
----------------
use Northwind

SELECT dbo.Customers.CustomerID, dbo.Customers.CompanyName, dbo.Orders.OrderID, dbo.Orders.OrderDate, dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity FROM dbo.Orders INNER JOIN dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID INNER JOIN dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID FOR xml raw,elements
-------------------
use Northwind

SELECT dbo.Customers.CustomerID, dbo.Customers.CompanyName, dbo.Orders.OrderID, dbo.Orders.OrderDate, dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity FROM dbo.Orders INNER JOIN dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID INNER JOIN dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID FOR XML RAW('Order'), ROOT('Orders')
---------------------
use Northwind

SELECT dbo.Customers.CustomerID, dbo.Customers.CompanyName, dbo.Orders.OrderID, dbo.Orders.OrderDate, dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity FROM dbo.Orders INNER JOIN dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID INNER JOIN dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID FOR XMl auto
---------------------
use Northwind

SELECT dbo.Customers.CustomerID, dbo.Customers.CompanyName, dbo.Orders.OrderID, dbo.Orders.OrderDate, dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity FROM dbo.Orders INNER JOIN dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID INNER JOIN dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID FOR XMl auto, elements
---------------------
use Northwind

SELECT dbo.Customers.CustomerID, dbo.Customers.CompanyName, dbo.Orders.OrderID, dbo.Orders.OrderDate, dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity FROM dbo.Orders INNER JOIN dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID INNER JOIN dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID FOR XML AUTO, ELEMENTS, ROOT('Orders')
-----------------------
use Northwind

use AdventureWorks SELECT EmployeeID "@EmpID", FirstName "EmpName/First", LastName "EmpName/Last" FROM Person.Contact INNER JOIN HumanResources.Employee ON Person.Contact.ContactID = Employee.ContactID FOR XML PATH
-----------------------
use AdventureWorks

SELECT EmployeeID "@EmpID", FirstName "EmpName/First", LastName "EmpName/Last" FROM Person.Contact INNER JOIN HumanResources.Employee ON Person.Contact.ContactID = Employee.ContactID FOR XML PATH('Employee')
-----------------------

No comments:

Follow by Email