Get Inspire insights from former attendees in our AMA discussion thread on Inspire Buzz. ACEs and other community members are on call all week to answer!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Extract Value Between Two Strings in XML

charlieepes
7 - Meteor

Hi:

I see many posts about extracting values between tags in XML but I cannot get any to suit my need.

My specific XML (split into rows) for ONE person among many:

<d:LoginName>i:0#.w|abcd\43983997</d:LoginName>
<d:Title>John Smith</d:Title>
<d:PrincipalType m:type="Edm.Int32">1</d:PrincipalType>
<d:Email>John.Smith@email.com</d:Email>

 

The XML really looks like this:
<d:LoginName>i:0#.w|abcd\43983997</d:LoginName><d:Title>John Smith</d:Title><d:PrincipalType m:type="Edm.Int32">1</d:PrincipalType><d:Email>John.Smith@email.com</d:Email>


I need one method or formula to extract the value for each tag Login Name, Title, PrincipalType, and Email, and put them into columns. I've seen many posts saying that it's not a good idea to parse xml with Regex, so I'm in need of a working method or formula. 

 

Thank you! 

2 REPLIES 2
MarqueeCrew
20 - Arcturus
20 - Arcturus

@charlieepes,

 

Note:  Since I saw the pipe character can exist in your data, I replaced the pipe with a comma

 

Without RegEx, I used replacements that ASSUME that the tags are constants.

 

 

 

Replace( 
	REPLACE(
		Replace(
			REPLACE(
				Replace([Field1], "<d:LoginName>",''),
        			              "</d:LoginName><d:Title>",','),
            	    	          '</d:Title><d:PrincipalType m:type="Edm.Int32">',','),
								  "</d:PrincipalType><d:Email>",','),
								  "</d:Email>", '')

 

 

 

Using your "raw" input, it creates:

 

 

i:0#.w|abcd\43983997,John Smith,1,John.Smith@email.com

 

 

 

From here, you should be able to use a Text to Columns tool and define the delimiter as a ',' comma.

 

Cheers,


Mark

 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
charlieepes
7 - Meteor

Mark, this is precisely what I needed, and I learn more problem solving by the second!! 

 

Thank you very much!  Cheers-

 

Charlie

Labels