Read typed XML in SQL Server 2008

Thursday, October 15, 2009 |


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/" >
            <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

0 comments: