USE Northwind

--WRITE XML
--Convert query results to XML
SELECT CustomerID, ContactName, CompanyName
FROM Customers
WHERE (CustomerID = N'ALFKI' 
	 OR CustomerID = N'ANATR')
ORDER BY CustomerID
FOR XML RAW -- RAW indicates a generic identifier <row /> as the element tag

--RETRIEVE XML
--Declare integer variable to point to XML string
DECLARE @TestDoc INT

--Parse XML String to return handle to @TestDoc
EXEC sp_xml_preparedocument @TestDoc OUTPUT, 
     N'<ROOT>
     <Employees EmpID="1234" FirstName="Ethan"       Dept="Marketing">
	</Employees>
     <Employees EmpID="1948" FirstName="Linda"       Dept="Research">
     </Employees>
     </ROOT>'

--Use SELECT statements with OPENXML function to query XML string 
SELECT *
FROM OPENXML(@TestDoc, N'/ROOT/Employees') 
WITH (EmpID NCHAR(5) './@EmpID',  --Declare data types for XML columns
      FirstName VARCHAR(50) './@FirstName', 
      Dept VARCHAR(10) './@Dept')

SELECT EmpID, FirstName
FROM OPENXML(@TestDoc, N'/ROOT/Employees') 
WITH (EmpID NCHAR(5) './@EmpID', 
      FirstName VARCHAR(50) './@FirstName', 
      Dept VARCHAR(10) './@Dept')
ORDER BY FirstName

--Save memory by removing internal XML representation
EXEC sp_xml_removedocument @TestDoc