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.

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/&#8221; >
            <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/&#8217; 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