tokenize string into key value pairs

Question

tokenize string into key value pairs

walpino-705684

Valued Member

Points: 71
More actions
August 12, 2016 at 2:20 am

#400566

hi everybody
we have to parse some strings with tsql
this are the strings I want to tokenize into key value pair tokens
<parameters>SearchName="Flat " SearchFormTemplateId="Search" SearchFormInfo=":ID_.Modules.Business.SearchModule.Label.SearchType.RE.Residential:LDSBL_15 km%15,10 km%10,5 km%5,20 km%20:LSTG_3,2,4:DR_3572ad9b-44cb-4229-a512-3037562d7cc7" SubTypeGroupId="3,2,4" InterfaceLocation="3.5908783699254903"§ CountryId="446931c9-7efe-440d-b674-24b8659087a7" Month="2000" AdMaterialTransactionTypeId="RE"/><azmi/></parameters>
<parameters>SearchName="Apartment and.. " CountryID="446931C9-7EFE-440D-B674-24B8659087A7" DefaultRegionID="446931C9-7EFE-440D-B674-24B8659087A7" CurrencyID="USD" LocationID="5A7FCB90-6247-44FD-AB2E-61FEABEA1246,AFAC41AE-0877-45E1-BAA6-1846E9D92332,CCB35927-94CE-4F41-B4BA-695A0396DC5A,B93B2C1F-460A-4726-8B51-FFBEFC15C810,4E701DB8-6B3B-4C0E-91FB-352BEF05FB40,9FA7B3DA-9D36-4742-8526-5143F01E6089,384AF508-D5DC-4ED1-93D2-635180447A8D,49B571CC-FC2E-40F0-A5A7-722D6D1F5926,B0CA4856-427B-4C93-A085-95FA36C082B8,3590075E-9A63-4AF5-BE88-9C8BB70BACB7,E1283086-8C38-4A97-B858-ABE5940F2986,78060DDC-3F00-4AF0-AD17-AC0007FE0622,FE815C24-94D5-4107-989D-B0F5FB5280E8,356F506E-09C1-4E1E-B54E-C54E80E502DF,F028F134-4C13-491B-846C-D85B4D979A5F,7E4013DB-2DE3-401B-9AFF-F41C16B12DB4,5C59BC43-63B7-45D5-8963-F916A71ED889,DA8D9F2D-1C43-443A-86C7-F9661FF06830" TransactionTypeID="RE" PropertySubTypeGroupID="1,2,3,4" MMAX="2100" MoMin="1500" NumberOfRoomsMin="3" InterfaceLocation="1"</parameters>
<parameters>SearchName="Apartment" DefaultRegionID="446931c9-7efe-440d-b674-24b8659087a7" CurrencyID="USD" TransactionTypeID="SE" PropertySubTypeGroupID="4" MMIN="100000" SearchBy="ByLocality" InterfaceLocation="1" LocationID="09194e31-1b5e-482b-9775-41886a1d1167" /><Keyid="6f4037e2-69ba-402d-a8ac-286b04e2bf3b"</parameters>
<parameters>SearchName="Location, 3 rooms" CurrencyID="USD" LocationID="f5812b35-e952-4e3e-a3f1-b0d678616826" TransactionTypeID="RE" PropertySubTypeGroupID="2,3" MMAX="2500" NumberOfRoomsMin="3" SurfaceLivibleMin="80" SearchBy="WithoutGeo" InterfaceLocation="2" /><Keyid="079bb71a-8f01-42fd-a956-0be2239be0ef"</parameters>
<parameters>SearchName="Flat..." DefaultRegionID="446931c9-7efe-440d-b674-24b8659087a7" CurrencyID="USD" TransactionTypeID="RE" PropertySubTypeGroupID="1,2,3" MMAX="4050" MoMin="2700" NumberOfRoomsMin="4.0" SearchBy="ByLocality" InterfaceLocation="1" LocationID="e22c7349-fd77-407a-88a4-bd39a105b986" /><Keyid="44e05bf1-8a26-48b6-b12b-5a652d586d78"</parameters>
<parameters>SearchName="Apartment -" DefaultRegionID="446931c9-7efe-440d-b674-24b8659087a7" CurrencyID="USD" TransactionTypeID="RE" PropertySubTypeGroupID="4" MoMin="500" SearchBy="ByLocality" InterfaceLocation="1" LocationID="e7148138-5be1-46c5-98b1-5ca2edeeb9da" /><Keyid="37ad0ed5-1800-417b-8e17-9ff5084086a2"</parameters>
<parameters>SearchName="Rent dsfsd" CurrencyID="USD" TransactionTypeID="RE" PropertySubTypeGroupID="4" SearchBy="WithoutGeo" /><Keyid="d695f924-6ce6-405b-a561-fa3269f41221"</parameters>
<parameters>SearchName="Apartement ..." DefaultRegionID="446931c9-7efe-440d-b674-24b8659087a7" CurrencyID="USD" AreaID="e90f1e83-ce84-46b6-826a-3775ffd2815d,dc2c2435-b773-424f-a279-4141efd21b78,ce142f26-2b08-419d-86a2-943bea72b279,b5749fe2-9360-48c0-8ffc-f6c9a343f2fa,a3a559c2-4c7e-4b4c-8314-335e9e655472,c3cd7728-3e6a-4787-959f-8e9e824b02d0" TransactionTypeID="RE" PropertySubTypeGroupID="3,2,4" MMAX="1500" LocationIdAgregate="" AreaIdAgregate="e90f1e83-ce84-46b6-826a-3775ffd2815d,dc2c2435-b773-424f-a279-4141efd21b78,ce142f26-2b08-419d-86a2-943bea72b279,b5749fe2-9360-48c0-8ffc-f6c9a343f2fa,a3a559c2-4c7e-4b4c-8314-335e9e655472,c3cd7728-3e6a-4787-959f-8e9e824b02d0" StateIdAgregate="1D6E6621-3179-4D54-A9D8-89E6F438FF3B,8C79C6B5-B61A-45F9-8560-91AE4825AC6A" InterfaceAreaIdAgregate="e90f1e83-ce84-46b6-826a-3775ffd2815d,dc2c2435-b773-424f-a279-4141efd21b78,ce142f26-2b08-419d-86a2-943bea72b279,b5749fe2-9360-48c0-8ffc-f6c9a343f2fa,a3a559c2-4c7e-4b4c-8314-335e9e655472,c3cd7728-3e6a-4787-959f-8e9e824b02d0" SearchBy="ByRegion" /><Keyid="37ad0ed5-1800-417b-8e17-9ff5084086a2"</parameters>
<parameters>SearchName="Apartment a" DefaultRegionID="446931c9-7efe-440d-b674-24b8659087a7" CurrencyID="USD" AreaID="49401e55-95c1-42cb-9242-9468707e7d8a" TransactionTypeID="SE" PropertySubTypeGroupID="16" AreaIdAgregate="49401e55-95c1-42cb-9242-9468707e7d8a" StateIdAgregate="97F9F29F-149E-4E53-A470-96CC8499A64B" SearchBy="ByRegion" /><Keyid="39fb945c-b048-4942-be86-17f692539ba3"</parameters>
<parameters>SearchName="Apartment, Location, 5 rooms" CountryID="446931C9-7EFE-440D-B674-24B8659087A7" DefaultRegionID="446931C9-7EFE-440D-B674-24B8659087A7" CurrencyID="USD" LocationID="E22C7349-FD77-407A-88A4-BD39A105B986" TransactionTypeID="RE" PropertySubTypeGroupID="1,2,3,4" NumberOfRoomsMin="5" InterfaceLocation="5"</parameters>
<parameters>SearchName="Apartment" DefaultRegionID="446931c9-7efe-440d-b674-24b8659087a7" CurrencyID="USD" TransactionTypeID="SE" PropertySubTypeGroupID="16" SearchBy="ByLocality" InterfaceLocation="5" LocationID="184e17b3-9d27-46fd-8a19-a9f402dfe680" /><Keyid="39fb945c-b048-4942-be86-17f692539ba3"</parameters>
it is not a real xml
the key and value are like key="value"
somethimes at the end there is some trash characters
for me the intersting part is from searchname=".."
till the following />
with xml nodes it doesent work neither with regex
has anybody any suggestions?
Thanks

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

That's a mighty big font you got there.

A complete solution will take some time but here's a couple techniques to break your input string into a seperate row for each "parameter group". Each solution uses delimitedSplit8K (see the link in my signature line to get the function).

-- OPTION 1: fix the data so it can be cast as XML and shred it

DECLARE @cleaned XML =

REPLACE(REPLACE(

'<parameters>SearchName="Flat " SearchFormTemplateId="Search" SearchFormInfo=":ID_.Modules.Business.SearchModule.Label.SearchType.RE.Residential:LDSBL_15 km%15,10 km%10,5 km%5,20 km%20:LSTG_3,2,4:DR_3572ad9b-44cb-4229-a512-3037562d7cc7" SubTypeGroupId="3,2,4" InterfaceLocation="3.5908783699254903"§ CountryId="446931c9-7efe-440d-b674-24b8659087a7" Month="2000" AdMaterialTransactionTypeId="RE"/><azmi/></parameters>

<parameters>SearchName="Apartment and.. " CountryID="446931C9-7EFE-440D-B674-24B8659087A7" DefaultRegionID="446931C9-7EFE-440D-B674-24B8659087A7" CurrencyID="USD" LocationID="5A7FCB90-6247-44FD-AB2E-61FEABEA1246,AFAC41AE-0877-45E1-BAA6-1846E9D92332,CCB35927-94CE-4F41-B4BA-695A0396DC5A,B93B2C1F-460A-4726-8B51-FFBEFC15C810,4E701DB8-6B3B-4C0E-91FB-352BEF05FB40,9FA7B3DA-9D36-4742-8526-5143F01E6089,384AF508-D5DC-4ED1-93D2-635180447A8D,49B571CC-FC2E-40F0-A5A7-722D6D1F5926,B0CA4856-427B-4C93-A085-95FA36C082B8,3590075E-9A63-4AF5-BE88-9C8BB70BACB7,E1283086-8C38-4A97-B858-ABE5940F2986,78060DDC-3F00-4AF0-AD17-AC0007FE0622,FE815C24-94D5-4107-989D-B0F5FB5280E8,356F506E-09C1-4E1E-B54E-C54E80E502DF,F028F134-4C13-491B-846C-D85B4D979A5F,7E4013DB-2DE3-401B-9AFF-F41C16B12DB4,5C59BC43-63B7-45D5-8963-F916A71ED889,DA8D9F2D-1C43-443A-86C7-F9661FF06830" TransactionTypeID="RE" PropertySubTypeGroupID="1,2,3,4" MMAX="2100" MoMin="1500" NumberOfRoomsMin="3" InterfaceLocation="1"</parameters>

<parameters>SearchName="Apartment" DefaultRegionID="446931c9-7efe-440d-b674-24b8659087a7" CurrencyID="USD" TransactionTypeID="SE" PropertySubTypeGroupID="4" MMIN="100000" SearchBy="ByLocality" InterfaceLocation="1" LocationID="09194e31-1b5e-482b-9775-41886a1d1167" /><Keyid="6f4037e2-69ba-402d-a8ac-286b04e2bf3b"</parameters>

<parameters>SearchName="Location, 3 rooms" CurrencyID="USD" LocationID="f5812b35-e952-4e3e-a3f1-b0d678616826" TransactionTypeID="RE" PropertySubTypeGroupID="2,3" MMAX="2500" NumberOfRoomsMin="3" SurfaceLivibleMin="80" SearchBy="WithoutGeo" InterfaceLocation="2" /><Keyid="079bb71a-8f01-42fd-a956-0be2239be0ef"</parameters>

<parameters>SearchName="Flat..." DefaultRegionID="446931c9-7efe-440d-b674-24b8659087a7" CurrencyID="USD" TransactionTypeID="RE" PropertySubTypeGroupID="1,2,3" MMAX="4050" MoMin="2700" NumberOfRoomsMin="4.0" SearchBy="ByLocality" InterfaceLocation="1" LocationID="e22c7349-fd77-407a-88a4-bd39a105b986" /><Keyid="44e05bf1-8a26-48b6-b12b-5a652d586d78"</parameters>

<parameters>SearchName="Apartment -" DefaultRegionID="446931c9-7efe-440d-b674-24b8659087a7" CurrencyID="USD" TransactionTypeID="RE" PropertySubTypeGroupID="4" MoMin="500" SearchBy="ByLocality" InterfaceLocation="1" LocationID="e7148138-5be1-46c5-98b1-5ca2edeeb9da" /><Keyid="37ad0ed5-1800-417b-8e17-9ff5084086a2"</parameters>

<parameters>SearchName="Rent dsfsd" CurrencyID="USD" TransactionTypeID="RE" PropertySubTypeGroupID="4" SearchBy="WithoutGeo" /><Keyid="d695f924-6ce6-405b-a561-fa3269f41221"</parameters>

<parameters>SearchName="Apartement ..." DefaultRegionID="446931c9-7efe-440d-b674-24b8659087a7" CurrencyID="USD" AreaID="e90f1e83-ce84-46b6-826a-3775ffd2815d,dc2c2435-b773-424f-a279-4141efd21b78,ce142f26-2b08-419d-86a2-943bea72b279,b5749fe2-9360-48c0-8ffc-f6c9a343f2fa,a3a559c2-4c7e-4b4c-8314-335e9e655472,c3cd7728-3e6a-4787-959f-8e9e824b02d0" TransactionTypeID="RE" PropertySubTypeGroupID="3,2,4" MMAX="1500" LocationIdAgregate="" AreaIdAgregate="e90f1e83-ce84-46b6-826a-3775ffd2815d,dc2c2435-b773-424f-a279-4141efd21b78,ce142f26-2b08-419d-86a2-943bea72b279,b5749fe2-9360-48c0-8ffc-f6c9a343f2fa,a3a559c2-4c7e-4b4c-8314-335e9e655472,c3cd7728-3e6a-4787-959f-8e9e824b02d0" StateIdAgregate="1D6E6621-3179-4D54-A9D8-89E6F438FF3B,8C79C6B5-B61A-45F9-8560-91AE4825AC6A" InterfaceAreaIdAgregate="e90f1e83-ce84-46b6-826a-3775ffd2815d,dc2c2435-b773-424f-a279-4141efd21b78,ce142f26-2b08-419d-86a2-943bea72b279,b5749fe2-9360-48c0-8ffc-f6c9a343f2fa,a3a559c2-4c7e-4b4c-8314-335e9e655472,c3cd7728-3e6a-4787-959f-8e9e824b02d0" SearchBy="ByRegion" /><Keyid="37ad0ed5-1800-417b-8e17-9ff5084086a2"</parameters>

<parameters>SearchName="Apartment a" DefaultRegionID="446931c9-7efe-440d-b674-24b8659087a7" CurrencyID="USD" AreaID="49401e55-95c1-42cb-9242-9468707e7d8a" TransactionTypeID="SE" PropertySubTypeGroupID="16" AreaIdAgregate="49401e55-95c1-42cb-9242-9468707e7d8a" StateIdAgregate="97F9F29F-149E-4E53-A470-96CC8499A64B" SearchBy="ByRegion" /><Keyid="39fb945c-b048-4942-be86-17f692539ba3"</parameters>

<parameters>SearchName="Apartment, Location, 5 rooms" CountryID="446931C9-7EFE-440D-B674-24B8659087A7" DefaultRegionID="446931C9-7EFE-440D-B674-24B8659087A7" CurrencyID="USD" LocationID="E22C7349-FD77-407A-88A4-BD39A105B986" TransactionTypeID="RE" PropertySubTypeGroupID="1,2,3,4" NumberOfRoomsMin="5" InterfaceLocation="5"</parameters>

<parameters>SearchName="Apartment" DefaultRegionID="446931c9-7efe-440d-b674-24b8659087a7" CurrencyID="USD" TransactionTypeID="SE" PropertySubTypeGroupID="16" SearchBy="ByLocality" InterfaceLocation="5" LocationID="184e17b3-9d27-46fd-8a19-a9f402dfe680" /><Keyid="39fb945c-b048-4942-be86-17f692539ba3"</parameters>',

'<Keyid','Keyid'),' "', '"');

WITH shredded AS

(

SELECT

parameterGroup = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),

line = XXX.value('.','varchar(4000)')

FROM (VALUES (@cleaned)) X(XX)

CROSS APPLY XX.nodes('parameters') X2(XXX)

)

SELECT parameterGroup, s.*

FROM shredded

CROSS APPLY dbo.DelimitedSplit8K(line,' ') s

WHERE parameterGroup = 1 -- I'm using this to just get the first "parameter group", remove it to get all of them

GO

-- OPTION 2: turn the data into text and use </parameter> for the split

DECLARE @cleaned varchar(8000) =

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(