Extract Value Between Two Strings in XML

[charlieepes]

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! 

[MarqueeCrew · Accepted solution]

@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

[charlieepes]

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

Thank you very much!  Cheers-

Charlie