Click here to Skip to main content
15,894,410 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello ,
How to break this xml tags in sql to get output.
XML
<text>
           <table border="1" width="100%">
             <thead>
               <tr>
                 <th>eMeasure Title</th>
                 <th>Version neutral identifier</th>
                 <th>eMeasure Version Number</th>
                 <th>Version specific identifier</th>
               </tr>
             </thead>
             <tbody>
               <tr>
                 <td>Preventive Care and Screening: Screening for Clinical Depression and Follow-Up Plan</td>
                 <td>9A031E24-3D9B-11E1-8634-00237D5BF174</td>
                 <td>3</td>
                 <td>40280381-3E93-D1AF-013E-9F642782222A</td>
                 <td />
               </tr>
               <tr>
                 <td>Colon Cancer: Chemotherapy for AJCC Stage III Colon Cancer Patients</td>
                 <td>8479F6D6-4200-4FD0-9438-30048EBE3E29</td>
                 <td>3</td>
                 <td>40280381-3D61-56A7-013E-6B81E6E455A5</td>
                 <td />
               </tr>
               <tr>
                 <td>HIV/AIDS: RNA Control for Patients with HIV</td>
                 <td>E0A07809-7B74-473F-BCC4-1891BE506AAA</td>
                 <td>2</td>
                 <td>40280381-3D61-56A7-013E-8AB774D0398A</td>
                 <td />
               </tr>
             </tbody>
           </table>
     </text>
Posted
Comments
_Asif_ 23-Sep-14 4:42am    
Question is not clear, what kind of output?

1 solution

You can use below XQuery aka XML query to get your result.


SQL
DECLARE @XmlDoc XML = '<text>
           <table border="1" width="100%">
             <thead>
               <tr>
                 <th>eMeasure Title</th>
                 <th>Version neutral identifier</th>
                 <th>eMeasure Version Number</th>
                 <th>Version specific identifier</th>
               </tr>
             </thead>
             <tbody>
               <tr>
                 <td>Preventive Care and Screening: Screening for Clinical Depression and Follow-Up Plan</td>
                 <td>9A031E24-3D9B-11E1-8634-00237D5BF174</td>
                 <td>3</td>
                 <td>40280381-3E93-D1AF-013E-9F642782222A</td>
                 <td />
               </tr>
               <tr>
                 <td>Colon Cancer: Chemotherapy for AJCC Stage III Colon Cancer Patients</td>
                 <td>8479F6D6-4200-4FD0-9438-30048EBE3E29</td>
                 <td>3</td>
                 <td>40280381-3D61-56A7-013E-6B81E6E455A5</td>
                 <td />
               </tr>
               <tr>
                 <td>HIV/AIDS: RNA Control for Patients with HIV</td>
                 <td>E0A07809-7B74-473F-BCC4-1891BE506AAA</td>
                 <td>2</td>
                 <td>40280381-3D61-56A7-013E-8AB774D0398A</td>
                 <td />
               </tr>
             </tbody>
           </table>
     </text>';

	 ;WITH CteData AS(
    
    SELECT
        Catalogs.value('(td)[1]', 'varchar(max)')  AS 'eMeasure Title',       
        Catalogs.value('(td)[2]', 'varchar(max)')  AS 'Version neutral identifier',       
		Catalogs.value('(td)[3]', 'int')  AS 'eMeasure Version Number',       
        Catalogs.value('(td)[4]', 'varchar(max)') AS 'Version specific identifier'
    FROM
        @XmlDoc.nodes('/text/table/tbody/tr') AS CatalogData(Catalogs))
        
   SELECT  *
      FROM CteData;
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900