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!
Solved! Go to Solution.
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
Mark, this is precisely what I needed, and I learn more problem solving by the second!!
Thank you very much! Cheers-
Charlie