Read XML Data in Sql Server

First of all we need to know about one  system procedure and a method
1-      OPENXML
2-      sp_xml_preparedocument
OPENXML – provides a rowset view over an XML document. Because OPENXML is a rowset provider,
sp_xml_preparedocument creates an internal representation of the XML image
A SELECT statement that uses an OPENXML rowset provider is then executed against the internal representation of the XML document
CREATE procedure GetXMLData(@Doc Varchar(max))
as
begin
DECLARE @idoc int
–Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @Doc
– Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT    *
FROM       OPENXML (@idoc, ‘/ROOT/Customer’,1)
WITH (CustomerID    varchar(20),
ContactName  varchar(20))
End
exec GetXMLData ‘

Order Date=”21/12/2011″>

Order Date=”21/12/2011″>



Result
T123    Shaneesh
T232   Krishna