如何在sql server中以td tr html格式破解xml标记 [英] how to break xml tag in td tr html format in sql server

问题描述

你好，

如何在sql中打破这个xml标签以获得输出。

Hello ,
How to break this xml tags in sql to get output.

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

推荐答案

你可以在XQue下面使用ry aka XML查询来获得你的结果。

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

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;

这篇关于如何在sql server中以td tr html格式破解xml标记的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！