none
POSTing http(s) request with xml content in Power Query RRS feed

  • Question

  • Hello all, I´ve a serious (<- for me) problem to formulate a working request in Power Query/M.

    Unfortunately I´m not familiar with M nor xml, but I found the following thread in here: https://social.technet.microsoft.com/Forums/en-US/4c93eb1f-508b-4c85-9a94-3c1233040efb/post-https-request-with-xml-content-in-power-query?forum=powerquery

    It seemed to be the right way so I took the code and modified it...   ...but it won´t work. The M-editor says that there is a problem with the syntax of the request argument. (expected token: Literal)

    Anyone any helpful ideas?

    let
        Quelle =Xml.Document(Web.Contents("http://bach-01.agentur.are:1411/",
    
    [Headers=(Content=Text.ToBinary("
    
    <?xml version="&Character.FromNumber(34)&"1.0"&Character.FromNumber(34)&"encoding="&Character.FromNumber(34)&"utf-8"&Character.FromNumber(34)&"standalone="&Character.FromNumber(34)&"yes"&Character.FromNumber(34)&"?>
      
    <bach:replication xmlns:bach="&Character.FromNumber(34)&"http://www.bach-software.at/replication"&Character.FromNumber(34)&"xmlns:xsi="&Character.FromNumber(34)&"http://www.w3.org/2001/XMLSchema-instance"&Character.FromNumber(34)&"xsi:schemaLocation="&Character.FromNumber(34)&"http://www.bach-software.at/replication xsds/replication.xsd"&Character.FromNumber(34)&">
    
        <auth>
             <organization>1234</organization>
             <username>ABCD</username>
             <password>EFGH</password>
        </auth>
    
    
        <request>
             <organization type="&Character.FromNumber(34)&"details"/>
        </request>
    
    </bach:replication>)]
    
                                                                               
    in
    # Quelle
    


    • Edited by Earthbug Sunday, October 22, 2017 6:47 AM
    Sunday, October 22, 2017 6:42 AM

Answers

  • Hi Earthbug. Try this:

    let
        Quelle = Xml.Document(
            Web.Contents(
                "http://bach-01.agentur.are:1411/",
                [
                    Content =
                        Text.ToBinary("<?xml version=""1.0"" encoding=""utf-8"" standalone=""yes""?>
                        <bach:replication xmlns:bach=""http://www.bach-software.at/replication"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xsi:schemaLocation=""http://www.bach-software.at/replication xsds/replication.xsd"">
                            <auth>
                                 <organization>1234</organization>
                                 <username>ABCD</username>
                                 <password>EFGH</password>
                            </auth>
                            <request>
                                 <organization type=""details""/>
                            </request>
                        </bach:replication>")
                ]
            )
        )
    in
        Quelle

    Ehren

    Monday, October 30, 2017 7:11 PM
    Owner

All replies

  • Hi Earthbug. Not sure if this is a step in the right direction, but at least it doesn't contain syntax errors. :)

    let
        Quelle = Xml.Document(
            Web.Contents(
                "http://bach-01.agentur.are:1411/",
                [
                    Headers = [
                        Content =
                            "<?xml version=""1.0"" encoding=""utf-8"" standalone=""yes""?>
                            <bach:replication xmlns:bach=""http://www.bach-software.at/replication"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xsi:schemaLocation=""http://www.bach-software.at/replication xsds/replication.xsd"">
                                <auth>
                                     <organization>1234</organization>
                                     <username>ABCD</username>
                                     <password>EFGH</password>
                                </auth>
                                <request>
                                     <organization type=""details""/>
                                </request>
                            </bach:replication>"
                    ]
                ]
            )
        )
    in
        Quelle


    Let me know if this works for you.

    Ehren


    Monday, October 23, 2017 7:32 PM
    Owner
  • Hello Ehren,

    thanks for your answer. Does it work? Yes an No...

    YES: The authentication seems to work but

    NO: Unfortunately the results are empty. The "query-part"

       <request>
               <organization type=""details""/>
        </request>

    seems to be without any function.  Do you have more ideas?

    Another question: As shown above, in my first version i adapted the tips of  https://social.technet.microsoft.com/Forums/en-US/4c93eb1f-508b-4c85-9a94-3c1233040efb/post-https-request-with-xml-content-in-power-query?forum=powerquery  (<-- sorry can´t manage it to post hyperlinks) and "filled" the original query with 

    Text.ToBinary

    and

    Character.FromNumber(34)

    To be honest - don´t know why and what it should do...   ...just trying to adapt the shown solution. PLease could you explain in short words what these arguments are good for (or not) and if they could be relevant to solve my problem.

    Many thanks and sunny regards, earthbug



    • Edited by Earthbug Wednesday, October 25, 2017 3:21 PM
    Wednesday, October 25, 2017 3:20 PM
  • It depends on what the web api you're querying. Do you have a sample of what kind of <request> payload you'd like to generate?

    Ehren

    Wednesday, October 25, 2017 11:30 PM
    Owner
  • Hi Ehren, many thanks for your feedback.

    The providers tutorial says the following:

    To start a query to the ARI, you must send an XML file with the following structure in the POST

    parameter:Then the code as posted above.

    The next information of the provider:
    Within the request block one or more queries can be sent to the bach server. If, for example, the tag <organization type = "details" /> is entered in the request block, then basic information about the operation is returned.
    
    The queries i can send are the following:
    
    In the request block of the query described above,different queries to the server. The following
    Attributes are:
    
        "type": Possible values ​​are "details" and "overview", with correspondingly detailed answers
        "range": Possible values ​​are "1" or "1-10". The result set is filtered using the primary key.
    
    The following queries are possible via the ARI:
    
        organization: Provides information about the operation
        revenue:Contains the complete movement data of the specified Z counter. becomes
        no Z counter is specified, the motion data of the current
        Day returned.
        articles: Contains the item master data.
        documents: Contains the receipts (receipts, invoices)
        vats: Contains the tax rates
        meccodegroups, meccodes: Contains the meccodes and meccodes
        grosscategories, categories, detailcategories: Contains the main sections, divisions, fine lines
    
    The
     Answers to these queries are one or more objects respective type. In the case of revenue, e.g. all bookings and
    Financial routes in an XMl structure.
    For me it seems that I don´t really have to formulate a query but "ask for some results" by sending the request-string.
    Thursday, October 26, 2017 3:51 PM
  • Hi Earthbug. It sounds like you may need to contact the api provider in order to determine what xml payload to send to the server. Once you figure out the payload, I'm happy to help you determine how to send it from PQ. However, figuring out what to send isn't really something I'm in a position to help you with, as I'm not familiar with the specifics of this particular API, and don't have a way to access it.

    Ehren

    Thursday, October 26, 2017 5:09 PM
    Owner
  • Hi Ehren, thanks for your answer again. I phoned with the hotline yesterday. What they told me: I should double check my script and try it again. Unfortunately they also do not know anything about MS Power Query.

    Not really helpful...

    You wanted to know what arguments have to be sent to the server. As shown below it´s just a simple string like <organization type="details" / > or one of the others (revenue, articles, ...).  If I do exactly what is shown on      http://confluence.gastro-mis.de/display/AIE/Ansprechen+des+ARI     the development and testing environment delivers sensful data.

    There has to be any way to reword or convert the xml-syntax that it works.

    Ehren, please would you take a quick look at the description on their homepage. It´s german, but you can see the xml code and query arguments that are working on the dev environment.

    Many thanks for your help and sunny regards, earthbug

     
    Saturday, October 28, 2017 4:39 AM
  • Hi Earthbug. Try this:

    let
        Quelle = Xml.Document(
            Web.Contents(
                "http://bach-01.agentur.are:1411/",
                [
                    Content =
                        Text.ToBinary("<?xml version=""1.0"" encoding=""utf-8"" standalone=""yes""?>
                        <bach:replication xmlns:bach=""http://www.bach-software.at/replication"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xsi:schemaLocation=""http://www.bach-software.at/replication xsds/replication.xsd"">
                            <auth>
                                 <organization>1234</organization>
                                 <username>ABCD</username>
                                 <password>EFGH</password>
                            </auth>
                            <request>
                                 <organization type=""details""/>
                            </request>
                        </bach:replication>")
                ]
            )
        )
    in
        Quelle

    Ehren

    Monday, October 30, 2017 7:11 PM
    Owner
  • Hi Ehren, sorry for the delay and the late answer. Yes - your last post contains the solution. To be honest, I can´t see the difference betwenn this and your earlier post at present, but I´ll take a second look later.

    Many thanks for your help, earthbug

    Sunday, November 12, 2017 1:34 PM