如何从SQL Server列中的html中获取数据 [英] how to get data from html in SQL Server column

问题描述

我在一个SQL Server列中有一些html内容，我想从html读取内容。

例如：

 < ektdesignns_choices ektdesignns_nodetype =elementtitle =你每个月玩多少演出？ ektdesignns_caption =你每个月玩多少演出？ name =ektpoll1303074024421ektdesignns_name =ektpoll1303074024421id =ektpoll1303074024421> 
 
< li> 
< input type =radioektdesignns_nodetype =itemname =ektpoll1303074024421value =1或less_1title =1或更少id =ID2504263/> 
< label contenteditable =trueunselectable =offfor =ID2504263> 1或更少< / label> 
< / li> 
< li> 
< input type =radioektdesignns_nodetype =itemname =ektpoll1303074024421value =2-4_2title =2-4id =ID5115606/> 
< label contenteditable =trueunselectable =offfor =ID5115606> 2-4< / label> 
< / li> 
< li> 
< input type =radioektdesignns_nodetype =itemname =ektpoll1303074024421value =5-7_3title =5-7id =ID477116/> 
< label contenteditable =trueunselectable =offfor =ID477116> 5-7< / label> 
< / li> 
< li> 
< input type =radioektdesignns_nodetype =itemname =ektpoll1303074024421value =8 or more_4title =8 or moreid =ID5515606/> 
< label contenteditable =trueunselectable =offfor =ID5515606> 8或更多< / label> 
< / li> 
< / ol> 
< / ektdesignns_choices>< input type =submitvalue =Vote/> 
  

我想阅读这个html中的所有标签。任何人都有任何想法，我该如何去解决它？解析方案

如果您的HTML确实符合XHTML标准，并且您拥有HTML存储在SQL Server表的 XML 列中，然后您可以使用XQuery在T-SQL中从中检索标签：

  DECLARE @HtmlTbl TABLE（ID INT IDENTITY，Html XML）
 
 INSERT INTO @HtmlTbl（Html）VALUES（'< ektdesignns_choices ektdesignns_nodetype =元素title =你每个月玩多少演出？ektdesignns_caption =你每个月玩多少演出？name =ektpoll1303074024421ektdesignns_name =ektpoll1303074024421id =ektpoll1303074024421> 
< ; ol contenteditable =falseonkeypress =design_validate_choice（1，-1，this，''Options are required。''）onclick =design_validate_choice（1，-1，this，''Options are required。''） onblur =design_validate_choice（1，-1，this，''Options are required。''）ektdesignns_validation =choice-reqekt designns_maxoccurs =1ektdesignns_minoccurs =1unselectable =ontitle =你每个月玩多少演出？class =design_list_vertical> 
< li> 
< input type =radioektdesignns_nodetype =itemname =ektpoll1303074024421value =1或less_1title =1或更少id =ID2504263/> 
< label contenteditable =trueunselectable =offfor =ID2504263> 1或更少< / label> 
< / li> 
< li> 
< input type =radioektdesignns_nodetype =itemname =ektpoll1303074024421value =2-4_2title =2-4id =ID5115606/> 
< label contenteditable =trueunselectable =offfor =ID5115606> 2-4< / label> 
< / li> 
< li> 
< input type =radioektdesignns_nodetype =itemname =ektpoll1303074024421value =5-7_3title =5-7id =ID477116/> 
< label contenteditable =trueunselectable =offfor =ID477116> 5-7< / label> 
< / li> 
< li> 
< input type =radioektdesignns_nodetype =itemname =ektpoll1303074024421value =8 or more_4title =8 or moreid =ID5515606/> 
< label contenteditable =trueunselectable =offfor =ID5515606> 8或更多< / label> 
< / li> 
 
< / ektdesignns_choices>< input type =submitvalue =Vote/>'）
  

这将从您的（X）Html中检索所有< label> 元素作为单个XML字符串：

  SELECT 
 Html.query（'// label'）
 FROM @HtmlTbl 
 WHERE ID = 1 $ 
 
 
 
 $ b 
输出： c $ c>< label contenteditable =trueunselectable =offfor =ID2504263> 1或更少< / label> 
< label contenteditable =trueunselectable =offfor =ID5115606> 2-4< / label> 
< label contenteditable =trueunselectable =offfor =ID477116> 5-7< / label> 
< label contenteditable =trueunselectable =offfor =ID5515606> 8或更多< / label> 
  

或者这会选择< label>的所有内容标签，每行一个：

  SELECT 
 C.value（'（。）[1 ]'，'varchar（1000）'）
 FROM @HtmlTbl 
 CROSS APPLY Html.nodes（'// label'）AS T（C）
 WHERE ID = 1 
  
 $ p $   1或更少
 2-4 
 5-7 
 8或更多
  




I have some html content in a SQL Server column, I want to read the content from the html.

For example:

<ektdesignns_choices ektdesignns_nodetype="element" title="How many gigs do you play each month?" ektdesignns_caption="How many gigs do you play each month?" name="ektpoll1303074024421" ektdesignns_name="ektpoll1303074024421" id="ektpoll1303074024421">
  <ol contenteditable="false" onkeypress="design_validate_choice(1, -1, this, 'Options are required.')" onclick="design_validate_choice(1, -1, this, 'Options are required.')" onblur="design_validate_choice(1, -1, this, 'Options are required.')" ektdesignns_validation="choice-req" ektdesignns_maxoccurs="1" ektdesignns_minoccurs="1" unselectable="on" title="How many gigs do you play each month?" class="design_list_vertical">
    <li>
      <input type="radio" ektdesignns_nodetype="item" name="ektpoll1303074024421" value="1 or fewer_1" title="1 or fewer" id="ID2504263" />
      <label contenteditable="true" unselectable="off" for="ID2504263">1 or fewer</label>
    </li>
    <li>
       <input type="radio" ektdesignns_nodetype="item" name="ektpoll1303074024421" value="2-4_2" title="2-4" id="ID5115606" />
       <label contenteditable="true" unselectable="off" for="ID5115606">2-4</label>
    </li>
    <li>
        <input type="radio" ektdesignns_nodetype="item" name="ektpoll1303074024421" value="5-7_3" title="5-7" id="ID477116" />
        <label contenteditable="true" unselectable="off" for="ID477116">5-7</label>
    </li>
    <li>
        <input type="radio" ektdesignns_nodetype="item" name="ektpoll1303074024421" value="8 or more_4" title="8 or more" id="ID5515606" />
        <label contenteditable="true" unselectable="off" for="ID5515606">8 or more</label>
    </li>
  </ol>
</ektdesignns_choices><input type="submit" value="Vote" />

I want read all the labels in this html. Anyone have any idea, how shall I go about it?

解决方案

If your HTML is indeed XHTML compliant, and if you have the HTML stored in a XML column in your SQL Server table, then you could retrieve your labels from it in T-SQL using XQuery:

DECLARE @HtmlTbl TABLE (ID INT IDENTITY, Html XML)

INSERT INTO @HtmlTbl(Html) VALUES('<ektdesignns_choices ektdesignns_nodetype="element" title="How many gigs do you play each month?" ektdesignns_caption="How many gigs do you play each month?" name="ektpoll1303074024421" ektdesignns_name="ektpoll1303074024421" id="ektpoll1303074024421">
  <ol contenteditable="false" onkeypress="design_validate_choice(1, -1, this, ''Options are required.'')" onclick="design_validate_choice(1, -1, this, ''Options are required.'')" onblur="design_validate_choice(1, -1, this, ''Options are required.'')" ektdesignns_validation="choice-req" ektdesignns_maxoccurs="1" ektdesignns_minoccurs="1" unselectable="on" title="How many gigs do you play each month?" class="design_list_vertical">
    <li>
      <input type="radio" ektdesignns_nodetype="item" name="ektpoll1303074024421" value="1 or fewer_1" title="1 or fewer" id="ID2504263" />
      <label contenteditable="true" unselectable="off" for="ID2504263">1 or fewer</label>
    </li>
    <li>
       <input type="radio" ektdesignns_nodetype="item" name="ektpoll1303074024421" value="2-4_2" title="2-4" id="ID5115606" />
       <label contenteditable="true" unselectable="off" for="ID5115606">2-4</label>
    </li>
    <li>
        <input type="radio" ektdesignns_nodetype="item" name="ektpoll1303074024421" value="5-7_3" title="5-7" id="ID477116" />
        <label contenteditable="true" unselectable="off" for="ID477116">5-7</label>
    </li>
    <li>
        <input type="radio" ektdesignns_nodetype="item" name="ektpoll1303074024421" value="8 or more_4" title="8 or more" id="ID5515606" />
        <label contenteditable="true" unselectable="off" for="ID5515606">8 or more</label>
    </li>
  </ol></ektdesignns_choices><input type="submit" value="Vote" />')

This will retrieve all <label> elements from your (X)Html as a single XML string:

SELECT
    Html.query('//label')
FROM @HtmlTbl 
WHERE ID = 1

Output:

<label contenteditable="true" unselectable="off" for="ID2504263">1 or fewer</label>
<label contenteditable="true" unselectable="off" for="ID5115606">2-4</label>
<label contenteditable="true" unselectable="off" for="ID477116">5-7</label>
<label contenteditable="true" unselectable="off" for="ID5515606">8 or more</label>

Or this will select all the contents of the <label> tags, one per row:

SELECT
    C.value('(.)[1]', 'varchar(1000)')
FROM @HtmlTbl 
CROSS APPLY Html.nodes('//label') AS T(C)
WHERE ID = 1

Output:

1 or fewer
2-4
5-7
8 or more

这篇关于如何从SQL Server列中的html中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！