Please help with XQuery Syntax

Question

Hi All,

Can anyone help me with the Xquery to extract the text "exec dbo.TestMeSP" from the XML below;

  <DTS:Executable>

  <DTS:ObjectData>

      <SQLTask:SqlTaskData xmlns:SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask" SQLTask:Connection="{BCCAE482-34A2-4700-881D-906050AB9F42}" SQLTask:TimeOut="0" SQLTask:IsStoredProc="False" SQLTask:BypassPrepare="True" SQLTask:SqlStmtSourceType="DirectInput" SQLTask:SqlStatementSource="exec dbo.TestMeSP" SQLTask:CodePage="1252" SQLTask:ResultType="ResultSetType_None" />

    </DTS:ObjectData>

  </DTS:Executable>

My attempt below gives me a null value

SELECT ExecSQL.GetProc.query('.')as LocXML,

ExecSQL

.GetProc.value('SqlStatementSource[1]', 'nvarchar(max)') as StoredProcName

FROM

#XmlParse CROSS APPLY XMLColumn.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts"; /DTS:Executable/DTS:Executable/DTS:ObjectData')  ExecSQL(GetProc)

Thanks for your help

Answer 1

First, your xml above doesn't declare the DTS namespace anywhere.  So including the declare statement does not make sense.  I have declared the xmlns in my query.

Next, you have two Executable nodes listed in your path.  There is only one

Next, SqlStatementSource. First this is an attribute so it must be referenced with an @ sign at the front.  Next, that attribute is in the SQLTask namespace, so you need to prefix the namespace declaration and you need to declare the namespace.  Finally, this attribute is a child of the SqlTaskData element.

I include two versions, one declaring the namespace in the xquery and one declaring it outside of the xquery.

CREATE TABLE #XmlParse (XMLColumn XML);
INSERT #XmlParse
VALUES (
'<DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts">
  <DTS:ObjectData>
      <SQLTask:SqlTaskData xmlns:SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask" SQLTask:Connection="{BCCAE482-34A2-4700-881D-906050AB9F42}" SQLTask:TimeOut="0" SQLTask:IsStoredProc="False" SQLTask:BypassPrepare="True" SQLTask:SqlStmtSourceType="DirectInput" SQLTask:SqlStatementSource="exec dbo.TestMeSP" SQLTask:CodePage="1252" SQLTask:ResultType="ResultSetType_None" />
    </DTS:ObjectData>
  </DTS:Executable>
');
SELECT ExecSQL.GetProc.query('.')as LocXML,
ExecSQL.GetProc.value('declare namespace SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask";( SQLTask:SqlTaskData/@SQLTask:SqlStatementSource)[1]', 'nvarchar(max)') as StoredProcName 
FROM 
#XmlParse CROSS APPLY XMLColumn.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts"; /DTS:Executable/DTS:ObjectData')  ExecSQL(GetProc) 
DROP TABLE #XmlParse;

I prefer to use the With XMLNamespaces statement, but it isn't required.

CREATE TABLE #XmlParse (XMLColumn XML);
INSERT #XmlParse
VALUES (
'<DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts">
  <DTS:ObjectData>
      <SQLTask:SqlTaskData xmlns:SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask" SQLTask:Connection="{BCCAE482-34A2-4700-881D-906050AB9F42}" SQLTask:TimeOut="0" SQLTask:IsStoredProc="False" SQLTask:BypassPrepare="True" SQLTask:SqlStmtSourceType="DirectInput" SQLTask:SqlStatementSource="exec dbo.TestMeSP" SQLTask:CodePage="1252" SQLTask:ResultType="ResultSetType_None" />
    </DTS:ObjectData>
  </DTS:Executable>
');
WITH xmlnamespaces ('www.microsoft.com/SqlServer/Dts' AS DTS,
'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask)
SELECT ExecSQL.GetProc.query('.')as LocXML,
ExecSQL.GetProc.value('(SQLTask:SqlTaskData/@SQLTask:SqlStatementSource)[1]', 'nvarchar(max)') as StoredProcName 
FROM 
#XmlParse CROSS APPLY XMLColumn.nodes('/DTS:Executable/DTS:ObjectData')  ExecSQL(GetProc) 
DROP TABLE #XmlParse;

---

Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008

Answer 2

Thanks Russ

Made the following mistakes: Did not declare the SQLTask namespace and did not add the @sign before the SQLStatement Attribute.

On your observations: The DTS:Executable actually has two nodes and the DTS namespace is declared at the beginning of the XML code which I did not want to show in full.

However, thanks for your explanation as I am able to getthe information I want now.