Read typed XML in SQL Server 2008

So far, I have written few articles on XML topic, before we move further, I would like to summarize those here with the respected links so if anybody interested, can have look at it.

Load relational XML data
Read XML node on same level
return comma separated value with “For XML Path”
update UnTyped XML data column

Apart from above four XML article, today I would like to introduce, how you can read typed XML in Microsoft SQL Server 2008.

I have one very small example to share with you. Have a look.

SET ANSI_NULLS ON

DECLARE @MyXML Xml

SET @MyXml =  

'<?xml version="1.0" encoding="utf-8"?>

      <L:Clients xmlns:L="http://sqlhub.com/client/" >

            <L:Body> 

                  <TestGroup xmlns="TestGroup">   

                        <Test1>TCLP VOA</Test1>   

                        <Test2>TCLP-SVOA</Test2>   

                        <Test3>Metals Group1</Test3>  

                  </TestGroup>

                  <TestGroup xmlns="TestGroup">   

                        <Test1>Cynide</Test1>   

                        <Test2>Mercury</Test2>   

                        <Test3>TO-15</Test3>  

                  </TestGroup>  

            </L:Body>

      </L:Clients>'

;WITH XMLNAMESPACES ('http://sqlhub.com/client/' as L,

                              'TestGroup' as TG)

SELECT      T.c.value('(TG:Test1)[1]', 'varchar(20)') as Test1,

            T.c.value('(TG:Test2)[1]', 'varchar(20)') as Test2,

            T.c.value('(TG:Test3)[1]', 'varchar(20)') as Test3

FROM      @MyXml.nodes('(L:Clients/L:Body/TG:TestGroup)') T(c)

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com