I have a skeleton of a code which I began to write for the following purpose:
use EAS
go
declare @entityID,int
declare @text,text
declare @col1,varchar --col1 is between <b>col1</b>
declare @col2,varchar --col2 are between >col2</td>
@entityID=entity_ID
@text = maAttributes_Text.value
@col1 = select SUBSTRING(@text,CHARINDEX('<b>',@text)+len('<b>'),
charindex('</b>',@text))
@col2 = select substring(@text, CHARINDEX('>',@text)+LEN('>'),charindex('</td>',@text))
begin
add to `table2` [entity_id], [caption], [value]
@entityID, @col1, @col2
end
I need to access a different table, table1, which contains fields entity_id, caption and value, as well as a few others which are unimportant.
entity_id | caption | value
----------|--------------|-------
12345 |Specifications| *html snippet*
What my code is supposed to do is, to find rows in table1 which have the value of 'Specifications' in field caption, then extract the entity_id value, store it, and then run a loop which takes all the rows of the html table and adds the info into another sql table.
For example, after running the code for one line of table1, table2 will look like as follows:
table2
entity_ID|Caption|Value
---------|-------|-----
12345 |length |10
12345 |width |10
12345 |height |10
et al.
Where all the information in table2 came from the html snippet which is stored in value field of table1.
My instincts tell me that I should use both a loop (I cannot make a loop in sql for the life of me) and the STRING_SPLIT function. However, I am not sure how to implement them.
After finishing the first row of table1, it will continue to the next row and the next row, as many rows WHERE caption in ('specifications')
EDIT: I understand my wording was pretty confusing. I will try to reexplain.
I have table1 which contains an ID number which is tied to an html snippet. The html snippet is the code for a table which contains many rows and two columns. My desired function will take the html snippet and parse it by row, and add it to table2 where the one ID number from table1 will be attached to the several rows of the html table. Therefore when the first few rows of the HTML SNIPPET are extracted, table2 will appear as so:
table2
entity_ID|Caption|Value
---------|-------|-----
12345 |length |10
12345 |width |10
12345 |height |10
wherein entity_ID is the unique ID number, and it is tied to several rows from within the HTML snippet. Caption is taken from col1 of the html snippet, and Value is taken from col2 of the html snippet.
EDIT 2:
A simple example of my situation:
table1
ID|Caption |Value
--|--------------|-----
1 |Specifications|*html snippet*
2 |Specifications|*html snippet2*
etc.
the code will take information from table1 and put it into `table2' as follows:
table2
ID|SpecName|Spec
--|--------|----
1 |Length |10
1 |Width |10
1 |Height |10
SpecName and Spec are fields from the html table that the html snippet created. Length, Width, Height are the information that the snippet is producing. 10, is the value for each of the SpecNames.
<table class="inline_sd_table"> <tr class="inline_sd_cell_row"> <td class="inline_sd_even_cell"><b>Accessories Optional</b></td> <td class="inline_sd_even_cell">Yes</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_odd_cell"><b>Accommodates Ceiling Height</b></td> <td class="inline_sd_odd_cell">N/A</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_even_cell"><b>ADA Compliance</b></td> <td class="inline_sd_even_cell">See Additional Information above</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_odd_cell"><b>Blower Air Mover Type</b></td> <td class="inline_sd_odd_cell">Fan</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_even_cell"><b>Blower Included</b></td> <td class="inline_sd_even_cell">Yes</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_odd_cell"><b>Boost Mode</b></td> <td class="inline_sd_odd_cell">No</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_even_cell"><b>CFM (Airflow)</b></td> <td class="inline_sd_even_cell">190</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_odd_cell"><b>Color/Finish</b></td> <td class="inline_sd_odd_cell">Almond</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_even_cell"><b>Control Feature Filter Reminder</b></td> <td class="inline_sd_even_cell">No</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_odd_cell"><b>Damper Included</b></td> <td class="inline_sd_odd_cell">Yes</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_even_cell"><b>Delay Shut Off</b></td> <td class="inline_sd_even_cell">No</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_odd_cell"><b>Digital Clock</b></td> <td class="inline_sd_odd_cell">No</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_even_cell"><b>Dishwasher Safe Filters</b></td> <td class="inline_sd_even_cell">Yes</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_odd_cell"><b>Duct Connector Size</b></td> <td class="inline_sd_odd_cell">3-1/4" x 10", 7" Round Non-Ducted</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_even_cell"><b>ENERGY STAR® qualified</b></td> <td class="inline_sd_even_cell">No</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_odd_cell"><b>Filter Included</b></td> <td class="inline_sd_odd_cell">Yes</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_even_cell"><b>Filter Type</b></td> <td class="inline_sd_even_cell">Aluminum</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_odd_cell"><b>Flue Accommodates ceilings up to xx ft</b></td> <td class="inline_sd_odd_cell">N/A</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_even_cell"><b>Heat Sentry - Temperature Sensor</b></td> <td class="inline_sd_even_cell">No</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_odd_cell"><b>HVI Certified</b></td> <td class="inline_sd_odd_cell">Yes</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_even_cell"><b>Light Bulb Quantity</b></td> <td class="inline_sd_even_cell">1</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_odd_cell"><b>Light Bulbs Included</b></td> <td class="inline_sd_odd_cell">No</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_even_cell"><b>Lighting Function</b></td> <td class="inline_sd_even_cell">One-level</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_odd_cell"><b>Lighting Type</b></td> <td class="inline_sd_odd_cell">Incandescent</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_even_cell"><b>Max Sones/100 CFM</b></td> <td class="inline_sd_even_cell">4.1</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_odd_cell"><b>Minimum Height Above Cook Top</b></td> <td class="inline_sd_odd_cell">18"</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_even_cell"><b>Mounting Type</b></td> <td class="inline_sd_even_cell">Under Cabinet</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_odd_cell"><b>Nightlight</b></td> <td class="inline_sd_odd_cell">No</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_even_cell"><b>Non-Stick Coated</b></td> <td class="inline_sd_even_cell">No</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_odd_cell"><b>Product Depth (inches)</b></td> <td class="inline_sd_odd_cell">17-1/2"</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_even_cell"><b>Product Height (inches)</b></td> <td class="inline_sd_even_cell">6"</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_odd_cell"><b>Product Width (inches)</b></td> <td class="inline_sd_odd_cell">42"</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_even_cell"><b>Rated Amps</b></td> <td class="inline_sd_even_cell">2.5</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_odd_cell"><b>Remote Control</b></td> <td class="inline_sd_odd_cell">No</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_even_cell"><b>Sones Certified Horizontal Rectangular High</b></td> <td class="inline_sd_even_cell">6.5</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_odd_cell"><b>Sones Certified Horizontal Rectangular Low</b></td> <td class="inline_sd_odd_cell">N/A</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_even_cell"><b>Sones Certified Vertical Rectangular High</b></td> <td class="inline_sd_even_cell">6.5</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_odd_cell"><b>Sones Certified Vertical Rectangular Low</b></td> <td class="inline_sd_odd_cell">N/A</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_even_cell"><b>Sones Certified Vertical Round High</b></td> <td class="inline_sd_even_cell">6.5</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_odd_cell"><b>Sones Certified Vertical Round Low</b></td> <td class="inline_sd_odd_cell">N/A</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_even_cell"><b>Switch Control Settings</b></td> <td class="inline_sd_even_cell">2</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_odd_cell"><b>Switch Style</b></td> <td class="inline_sd_odd_cell">Rocker</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_even_cell"><b>Timer</b></td> <td class="inline_sd_even_cell">No</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_odd_cell"><b>UL Listed</b></td> <td class="inline_sd_odd_cell">Yes</td> </tr> <tr class="inline_sd_cell_row"> <td class="inline_sd_even_cell"><b>Voltage</b></td> <td class="inline_sd_even_cell">120</td> </tr> </table> 
