Question on filtering using Query XML action.

  • 31 January 2018
  • 9 replies
  • 14 views

Badge +8

Hi all so I have a rather large XML file (about 253k rows) stored in SharePoint library. I'm pointing to it and connecting just fine but clearly I need to filter this to a manageble size. I've pasted the schema template below. 

Basically I have a SharePoint list that this workflow is attached to. I would like to filter using the Last Logged in User to pull any Installed apps that are associated with that UserName. Ideally I would put the username in my list field click submit fire the workflow and have it spit out in a text file the filtered XML that has that User's applications that are associated with them. This make sense? Can it be done this way. Thanks in advance.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<DATA>

       <LAST_LOGGED_IN_USER ID="UserName">

                                   <COMPUTER_NAME>"Name"</COMPUTER_NAME>

                        <INSTALLED_APPS_WINDOWS_FRS>Adobe Acrobat Reader<INSTALLED_APPS_WINDOWS_FRS>

        <LAST_LOGGED_IN_USER>

        <LAST_LOGGED_IN_USER ID="UserName">

                                   <COMPUTER_NAME>"Name"</COMPUTER_NAME>

                         <INSTALLED_APPS_WINDOWS_FRS>Adobe Acrobat Reader<INSTALLED_APPS_WINDOWS_FRS>

        <LAST_LOGGED_IN_USER>

</DATA>


9 replies

Userlevel 5
Badge +14

I assume it's just a copy&paste problem, but your above snippet is not a valid XML - it misses closing tags for some nodes.

so I have made it first valid XML as follows

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<DATA>
  <LAST_LOGGED_IN_USER ID="UserName">
    <COMPUTER_NAME>"Name"</COMPUTER_NAME>
    <INSTALLED_APPS_WINDOWS_FRS>Adobe Acrobat Reader</INSTALLED_APPS_WINDOWS_FRS>
  </LAST_LOGGED_IN_USER>
  <LAST_LOGGED_IN_USER ID="UserName">
    <COMPUTER_NAME>"Name"</COMPUTER_NAME>
    <INSTALLED_APPS_WINDOWS_FRS>Adobe Acrobat Reader</INSTALLED_APPS_WINDOWS_FRS>
  </LAST_LOGGED_IN_USER>
</DATA>

for such a XML following XPath should work

note I used static string 'UserName' but you can replace it with list field or workflow variable reference

//LAST_LOGGED_IN_USER[@ID='UserName']/INSTALLED_APPS_WINDOWS_FRS

213155_pastedImage_1.png

Badge +8

Thank you Marian you're correct I typed it in very quickly. Thank you for responding. I entered it in as you wrote it I substituted the username for a list field as you mentioned. I don't get any Output though. Again I'm pullling from a very large xml file on the list as my XML source. Could that make it any different?

Userlevel 5
Badge +14

size shouldn't be a problem. if it was I'd expect action to fail.

check what's the real value of user list field (write it to log) and make sure there exists an entry in source XML.

make sure your XML source fits to the snippet you provided and do not have any other typo there or any other level of nesting.

hopefully you ran the workflow and haven't just tested with run now within designer.

Badge +8

Thank you very much Marian that worked. It was a typo in the source XML. Just one last question perhaps you know, the data is being spit out correctly now but it's coming out right to left so it looks very jumbled. Any way to express the data up to down nice and neatly? I can't see how to do it in the Collection Operation. 

Again many many thanks for your help.

Userlevel 5
Badge +14

glad to help laugh.png

I'm not sure what you mean. can post post some sample what you get and what you'd like to get?

Badge +8

Sure no problem. So I'm placing that collection into a rich text box in an Infopath form. The data comes out but unfortunately it's coming out side to side and word wrapped. I would really love it if it would be single lines. So in other words it looks like this;

Microsoft Office|Adobe Reader|InfoPath|Mozilla Firefox

When I would really like it to be like:

Microsoft Office

Adobe Reader

InfoPath

Mozilla Firefox

Using Nintex I can separate between each piece of software with whatever I choose (I chose brackets) and when I execute it within the XML Query control it does come out nicely like that but when it's put in the field it does not. This feels like one of those very simple fixes but I'm not sure. That make sense?

Userlevel 5
Badge +14

that should be easy.

you have stored output of XML query in collection variable. collection is moreless just semicolon separated list of elements.

so you can use regular expression action to turn collection into mutliline string. something like this

213177_pastedImage_1.png

note, I'm not sure how infopath handles line breaks. maybe you will need to replace " " with a "<BR>" tag or NewLine reference.

Badge +8

You did it!!! the <BR> tag worked like a charm. Thank you SO much Marian you helped fix a very persistent problem. You are awesome. Have a great weekend. : )

Userlevel 5
Badge +14

happy.png

Reply