1

Below is the XML snippet of an SSIS package; I'm trying to write an XPath query to find all "SELECT *" statements from the SSIS package.

I'm getting below error

ERROR: Caused by: com.sun.org.apache.xpath.internal.domapi.XPathStylesheetDOM3Exception: Prefix must resolve to a namespace: SQLTask

XML:

    <DTS:Executable
      DTS:refId="Package\TEST\TEST_COUNT"
      <DTS:ObjectData>
        <SQLTask:SqlTaskData
          SQLTask:Connection="{F7343EC3-A89E-4236-962C-FD2EB8B9491E}"
          SQLTask:SqlStatementSource="select * from dbo.TEST where colname1=? and colname2=?"
          SQLTask:ResultType="ResultSetType_SingleRow" xmlns:SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask">
        </SQLTask:SqlTaskData>
      </DTS:ObjectData>
    </DTS:Executable>

Xpath query:

//SQLTask:SqlTaskData[contains(translate(@SQLTask:SqlStatementSource,'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'abcdefghijklmnopqrstuvwxyz') ,"select *")]/../../@DTS:refId

I was able to evaluate the xpath expression successfully from https://www.freeformatter.com/xpath-tester.html. However the actual packages are validated through XPath rules defined in SonarQube from where I'm getting this error.

Leo
  • 868
  • 1
  • 13
  • 32
  • 1
    You are using the `SQLTask:` prefix without declaring it. In other words, the error message means that SonarQube has no idea which namespace this prefix refers to. Search how to declare XML namespaces in SonarQube. – Tomalak Sep 11 '18 at 11:29
  • @Tomalak but the same expression worked when I uploaded the xml file in https://www.freeformatter.com/xpath-tester.html – Leo Sep 11 '18 at 11:31
  • 1
    This might be, but freeformatter and SonarQube are not the same thing. – Tomalak Sep 11 '18 at 11:32
  • 2
    There's no mention of XML namespace support in [SonarQube's documentation](https://docs.sonarqube.org/display/DEV/Adding+Coding+Rules+using+XPath), so if you cannot fix your XML to make it [***namespace-well-formed***](https://stackoverflow.com/a/25830482/290085), you may have to use `local-name()` to [defeat namespaces in XPath as described in this answer](https://stackoverflow.com/q/40796231/290085). – kjhughes Sep 11 '18 at 12:38
  • 1
    Could you figure it out or do you still need an answer? – wp78de Sep 12 '18 at 03:40
  • @wp78de Couldn't figure out.. Moreover I'm just a beginner with Xpath. Appreciate support :) – Leo Sep 12 '18 at 11:32

1 Answers1

1

Here is your XPath query with skirted namespaces as pointed out in comments:

//*:SqlTaskData[contains(translate(@*:SqlStatementSource,'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'abcdefghijklmnopqrstuvwxyz') ,"select *")]/../../@*:refId

For an explanation, read kjhughes excellent answer here.

Now, getting all SqlStatementSource that contain "select *" works the same way:

//*:SqlTaskData[contains(translate(@*:SqlStatementSource,'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'abcdefghijklmnopqrstuvwxyz') ,"select *")]/@*:SqlStatementSource

Online Demo

or use the local-name() function as you already found out:

 //*[local-name()='SqlTaskData'][contains(translate(@*[local-name()='SqlStatementSource'],'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'abcdefghijklmnopqrstuvwxyz') ,"select count(*)")]/@*[local-name()='SqlStatementSource']
wp78de
  • 18,207
  • 7
  • 43
  • 71
  • I tried this expression, it is working freeformatter.com/xpath-tester.html & also in the online demo link you shared, but when I executed sonar scanner, it's still throwing error: com.sun.org.apache.xpath.internal.domapi.XPathStylesheetDOM3Exception: Prefix must resolve to a namespace: ERROR: Caused by: Prefix must resolve to a namespace: Could it be something to do at SonarQube admin level? – Leo Sep 13 '18 at 08:25
  • I replaced the attributes in the expression with local-name() function and it worked //*[local-name()='SqlTaskData'][contains(translate(@*[local-name()='SqlStatementSource'],'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'abcdefghijklmnopqrstuvwxyz') ,"select count(*)")]/@*[local-name()='SqlStatementSource'] . However now the sonarqube showing "There is a pending analysis. More details available on the Background Tasks page" :D – Leo Sep 13 '18 at 09:43
  • Can you put your solution as Answer, I can mark it correct and close the question. Anyway the thing worked.. Thank you very much :) – Leo Sep 14 '18 at 09:29