Separate/Split single field in to multiple fields

I have a table 'Waterwatch_Active' that has a field 'Att'.

The Att field is a complex concatenated field (it looks like html actually) that I need to split out in to:

This is an example of the Att field of my table Waterwatch_Active Waterwatch_active example

textattributes>n <li><strong><span class="atr-name">site_name</span>:</strong> <span class="atr-value">Walkers Lake</span></li>n <li><strong><span class="atr-name">site_code</span>:</strong> <span class="atr-value">NC_LWA001</span></li>n n n <li><strong><span class="atr-name">basin_name</span>:</strong> <span class="atr-value">Avoca River</span></li>n n n n <li><strong><span class="atr-name">water_type</span>:</strong> <span class="atr-value">Other</span></li>n <li><strong><span class="atr-name">lng</span>:</strong> <span class="atr-value">142.923793</span></li>n <li><strong><span class="atr-name">lat</span>:</strong> <span class="atr-value">-36.540724</span></li>n <li><strong><span class="atr-name">zone</span>:</strong> <span class="atr-value">54</span></li>n n n <li><strong><span class="atr-name">public_url</span>:</strong> <span class="atr-value">http://www.vic.waterwatch.org.au/site/170005</span></li>n</ul>

textattributes>n <li><strong><span class="atr-name">site_name</span>:</strong> <span class="atr-value">Murray River behind Koondrook PS</span></li>n <li><strong><span class="atr-name">site_code</span>:</strong> <span class="atr-value">NC_MUR400</span></li>n n n <li><strong><span class="atr-name">basin_name</span>:</strong> <span class="atr-value">Loddon River</span></li>n <li><strong><span class="atr-name">catchment_name</span>:</strong> <span class="atr-value">Loddon River</span></li>n <li><strong><span class="atr-name">sub_catchment_name</span>:</strong> <span class="atr-value">Loddon River</span></li>n <li><strong><span class="atr-name">reach_name</span>:</strong> <span class="atr-value">7_38 Gunbower Creek</span></li>n <li><strong><span class="atr-name">water_type</span>:</strong> <span class="atr-value">Other</span></li>n <li><strong><span class="atr-name">lng</span>:</strong> <span class="atr-value">144.130368</span></li>n <li><strong><span class="atr-name">lat</span>:</strong> <span class="atr-value">-35.633233</span></li>n <li><strong><span class="atr-name">zone</span>:</strong> <span class="atr-value">55</span></li>n n n <li><strong><span class="atr-name">public_url</span>:</strong> <span class="atr-value">http://www.vic.waterwatch.org.au/site/170021</span></li>n</ul>


I have figured out how to pick out one at a time but I need it to all happen at once and produce a single resulting table. This is how I've managed to get site_name out:

--site_name
DECLARE @SiteNamekeysValueToSearch NVARCHAR(4000) = '"atr-value">'
DECLARE @SiteNameuntilThisCharAppears NVARCHAR(4000) = '</span>'
DECLARE @SiteNamekeysValueToSearchPattern NVARCHAR(4000) = '%' + @SiteNamekeysValueToSearch + '%'

SELECT SUBSTRING(
Att,
PATINDEX(@SiteNamekeysValueToSearchPattern, Att) + LEN(@SiteNamekeysValueToSearch),
CHARINDEX(
@SiteNameuntilThisCharAppears,
Att,
PATINDEX(@SiteNamekeysValueToSearchPattern, Att) + LEN(@SiteNamekeysValueToSearch)
) -(PATINDEX(@SiteNamekeysValueToSearchPattern, Att) + LEN(@SiteNamekeysValueToSearch))
)
FROM Waterwatch_Active
--End site_name


So ideally I'd like my resulting table to look like this
Waterwatch_Active result

+----------------------------------+-----------+
| Site_Name | Site_Code |
+----------------------------------+-----------+
| Walkers Lake | NC_LWA001 |
| Murray River behind Koondrook PS | NC_MUR400 |
+----------------------------------+-----------+


Please any help will be greatly appreciated, I'm in over my head but I'm determined not to give up.

1 Answer
1

Assuming you are processing this row by row, then you can use the following to get the site_name. You can modify it to get other attributes.

The code converts the string into a valid xml string (it needs a <ul> at the front, then uses an xpath query to get the value of the site_name:

<ul>

Substituent your string for your (almost) xml string.

declare @xml xml
set @xml=convert(xml,'<ul>'+'*your string*')
select @xml.value('(/ul/li/strong/span[text()="site_name"]/../../span)[1]','varchar(100)')


If you want to do this against a table:

select convert(xml,'<ul>'+att).value('(/ul/li/strong/span[text()="site_name"]/../../span)[1]','varchar(100)') as SiteName
from waterwatch_active


lastly, if you are doing multiple columns, such as site_name and site_code, do this:

;with attTable as (select convert(xml,'<ul>'+att) as attXML from waterwatch_active)
select attXML.value('(/ul/li/strong/span[text()="site_name"]/../../span)[1]','varchar(100)') as SiteName,
attXML.value('(/ul/li/strong/span[text()="site_code"]/../../span)[1]','varchar(100)') as SiteCode
from attTable


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.