none
Help Navigating XML File with SQL nodes() method against a column of XML type RRS feed

  • Question

  • I have a XML file (download XML File below at URL) stored in a SQL Column of XML type. The column with the XML file is named CurrentPackageXML in a table called dbo.PackageInformation.

    I want to query this XML file like in the XML Example from the Microsoft Library I provide below, but return ALL of the entries for the DTS:ConnectionManager node. I think there is something wrong with how I map the nodes like in the Microsoft Library example '/MI:root/MI:Location'.

    Can someone also verify if I declared my namespace correct when working with a SSIS (DTS) XML file?

    My query is as follows:

    SELECT R.query('.') as Result
    FROM dbo.PackageInventory
    CROSS APPLY CurrentPackageXML.nodes('
    declare namespace DTS="http://www.microsoft.com/SqlServer/Dts";
    /DTS:Executable/DTS:ConnectionManagers/DTS:ConnectionManager/DTS:ObjectData/DTS:ConnectionManager') as Result(R)

    Get the XML file here... http://www.aimreportwriting.com/samplexml/SampleSSISXML.xml

    Note: My basic query above was taken from the Microsoft Library Page below in the Page Section Header Titled, "Specifying the nodes() method against a column of xml type".

    nodes() Method (xml Data Type)

    SELECT C.query('.') as result
    FROM Production.ProductModel
    CROSS APPLY Instructions.nodes('
    declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
    /MI:root/MI:Location') as T(C)
    WHERE ProductModelID=7


    Ryan P. Casey • <a href="http://www.R-P-C-Group.com">www.R-P-C-Group.com</a>

    Friday, July 24, 2015 11:42 PM

Answers

  • It seems that you are somewhat randomly addressing nodes in the XML document. This can be done, if you prefix with the wild-card path, //. But this is not so good for performance, and you may get unintended matches.

    Here is a query that returns some data that you can work from:

    ;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts'AS DTS)
    SELECT P.p.value('@DTS:Name', 'nvarchar(30)') AS Name,
           P.p.value('(./text())[1]', 'int') AS Version,
           CM1.cm1.value('@DTS:refId', 'nvarchar(50)') AS refid,
           CM2.cm2.value('@DTS:ConnectionString', 'nvarchar(150)') AS ConnectionString
    FROM   @PackageCode t
    CROSS  APPLY t.PackageXML.nodes('/DTS:Executable/DTS:Property') AS P(p)
    CROSS  APPLY PackageXML.nodes('/DTS:Executable/DTS:ConnectionManagers/DTS:ConnectionManager') CM1(cm1)
    CROSS  APPLY CM1.cm1.nodes('DTS:ObjectData/DTS:ConnectionManager') AS CM2(cm2)

    When you need data from multiple levels, you work your way down with nested CROSS APPLY as illustrated by the second and third CROSS APPLY. (Or OUTER APPLY if a node is optional.)

    The first CROSS APPLY here is a bit out of place. It works, because there only seems to be one instance of  DTS:Property, but had there been two, you would have gotten all possible combinations of the first and the second/third. Thus, normally you should only one CROSS APPLY from a certain node.

    In your examples you add XPath selections, but I am not close to understand what you intended with these, so I leave them for now.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by RPCASEY001 Sunday, July 26, 2015 1:06 AM
    Saturday, July 25, 2015 10:05 AM

All replies

  • My issue with my query is that it returns no data.

    Also, the Microsoft Library says... "This query returns the context item, in which the abbreviated syntax '.' for self::node() is specified, in the query() method."

    What does this article mean by context item?

    Can someone explain what this means... for self::node() is specified, in the query() method


    Ryan P. Casey • <a href="http://www.R-P-C-Group.com">www.R-P-C-Group.com</a>

    Friday, July 24, 2015 11:49 PM
  • I am wondering if this is a method that will work????

    CREATE PROC GetConnectionstringDetailsfromSSIS
    @PackagePath varchar(200)
    AS
    DECLARE @PackageCode table
    (
    PackageXML xml
    )
    DECLARE @Params nvarchar(100) = N'@PackagePath varchar(200)',@SQL nvarchar(4000)=N'SELECT *
    FROM OPENROWSET(BULK ''' + @PackagePath  + N''',SINGLE_BLOB) AS a'
    INSERT @PackageCode
    EXEC sp_executesql  @SQL,@Params,@PackagePath =@PackagePath
      
    ;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS)
    SELECT t.ConnStr AS ConnectionString,t.ConnID AS ConnectionManagerID,
    b.value('@name','varchar(100)') AS ConnectionType
    FROM
    (
    SELECT PackageXML,q.value('.','varchar(1000)') AS ConnStr,
     n.value('DTS:Property[@DTS:Name="DTSID"][1]','varchar(50)') AS ConnID
    FROM @PackageCode t
    CROSS APPLY PackageXML.nodes('/DTS:Executable/DTS:ConnectionManager')m(n)
    CROSS APPLY n.nodes('DTS:ObjectData/DTS:ConnectionManager/DTS:Property[@DTS:Name="ConnectionString"]')p(q)
    WHERE p.q.value('.','varchar(100)') > ''
    )t
    OUTER APPLY PackageXML.nodes('//connections/connection[@connectionManagerID=sql:column("ConnID")]')a(b)
    GO


    Ryan P. Casey • <a href="http://www.R-P-C-Group.com">www.R-P-C-Group.com</a>

    Friday, July 24, 2015 11:54 PM
  • This is how I tried to use the sample above with my XML file I provided in the original Post.

    WITH XMLNAMESPACES ('http://www.microsoft.com/SqlServer/Dts' AS DTS)
    
    SELECT t.ConnStr AS ConnectionString,t.ConnID AS ConnectionManagerID,
    
    b.value('@name','varchar(100)') AS  ConnectionType
    
    FROM
    
    (
    
    SELECT CurrentPackageXML,q.value('.','varchar(1000)') AS  ConnStr,
    
     n.value('DTS:Property[@DTS:Name="DTSID"][1]','varchar(50)') AS  ConnID
    
    FROM [dbo].[PackageInventory] t
    
    CROSS APPLY CurrentPackageXML.nodes('/DTS:Executable/DTS:ConnectionManager')m(n)
    
    CROSS APPLY n.nodes('DTS:ObjectData/DTS:ConnectionManager/DTS:Property[@DTS:Name="ConnectionString"]')p(q)
    
    WHERE p.q.value('.','varchar(100)') > ''
    
    )t
    


    Ryan P. Casey • <a href="http://www.R-P-C-Group.com">www.R-P-C-Group.com</a>

    Saturday, July 25, 2015 12:13 AM
  • Still now rows returned from my XML file...


    Ryan P. Casey • <a href="http://www.R-P-C-Group.com">www.R-P-C-Group.com</a>

    Saturday, July 25, 2015 12:13 AM
  • This is a SSIS 2012 XML format.

    Ryan P. Casey • <a href="http://www.R-P-C-Group.com">www.R-P-C-Group.com</a>

    Saturday, July 25, 2015 1:11 AM
  • It seems that you are somewhat randomly addressing nodes in the XML document. This can be done, if you prefix with the wild-card path, //. But this is not so good for performance, and you may get unintended matches.

    Here is a query that returns some data that you can work from:

    ;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts'AS DTS)
    SELECT P.p.value('@DTS:Name', 'nvarchar(30)') AS Name,
           P.p.value('(./text())[1]', 'int') AS Version,
           CM1.cm1.value('@DTS:refId', 'nvarchar(50)') AS refid,
           CM2.cm2.value('@DTS:ConnectionString', 'nvarchar(150)') AS ConnectionString
    FROM   @PackageCode t
    CROSS  APPLY t.PackageXML.nodes('/DTS:Executable/DTS:Property') AS P(p)
    CROSS  APPLY PackageXML.nodes('/DTS:Executable/DTS:ConnectionManagers/DTS:ConnectionManager') CM1(cm1)
    CROSS  APPLY CM1.cm1.nodes('DTS:ObjectData/DTS:ConnectionManager') AS CM2(cm2)

    When you need data from multiple levels, you work your way down with nested CROSS APPLY as illustrated by the second and third CROSS APPLY. (Or OUTER APPLY if a node is optional.)

    The first CROSS APPLY here is a bit out of place. It works, because there only seems to be one instance of  DTS:Property, but had there been two, you would have gotten all possible combinations of the first and the second/third. Thus, normally you should only one CROSS APPLY from a certain node.

    In your examples you add XPath selections, but I am not close to understand what you intended with these, so I leave them for now.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by RPCASEY001 Sunday, July 26, 2015 1:06 AM
    Saturday, July 25, 2015 10:05 AM
  • Erland, that is exactly what I needed! Thank you so much. I am just learning XML, so would you help me understand a couple of things?

    The Microsoft Library Article mentioned above says... "This query returns the context item, in which the abbreviated syntax '.' for self::node() is specified, in the query() method."

    What does this article mean by context item? How is the content item relevant to the XML you just provided me to solve my issue?

    Can you explain what this means... self::node()?

    Is this a syntax for something and what are other possibilities for this?

    You use CM1.cm1 in your SQL. I have seen people just use the cm1. Why do you use both?  Is that a type of Alias? What type of Alias is it? What are the names for the outer and inner values CM1.cm1 CM1(cm1). Why is a cross apply aliased in this way? It is also kinda acting like a CTE in that it can be called later in the next node method.


    Ryan P. Casey • <a href="http://www.R-P-C-Group.com">www.R-P-C-Group.com</a>

    Sunday, July 26, 2015 1:05 AM
  • Let's start with CROSS APPLY. The APPLY operator is like a join operator, but the difference is that the right-hand side can refer to the left-hand side. For instance, this query return the most recent order for all customers:

       SELECT C.CustomerName, O.OrderID, O.OrderDate, O.EmployeeID
       FROM   Customers C
       CROSS  APPLY (SELECT TOP 1 O.OrderID, O.OrderDate, O.EmployeeID
                     FROM   Orders O
                     WHERE  O.CustomerID = C.CustomerID
                     ORDER  BY O.OrderID DESC) AS O

    This query will not include customers who have placed no orders at all; they are included if you change CROSS APPLY to OUTER APPLY.

    CROSS APPLY can also be used to call table-valued functions and passing columns from another table as argument, and this is what happens in the case with .nodes.

    Consider this XML document:

    DECLARE @x xml =
    '<First>
       <Second attr="value">
          <Third attr="one third">First element in a third</Third>
          <Third attr="two thirds">Second element in a third</Third>
          <Third attr="One">Third element in a third</Third>
       </Second>
       <Second attr="second value">
          <Third attr="another third">Fourth element in a third</Third>
          <Third attr="yet another third">Fifth element in a third</Third>
          <Third attr="Two">Sixth element in a third</Third>
       </Second>
    </First>'

    Then run this query:

    SELECT S.s.query('.')
    FROM   @x.nodes('/First/Second') AS S(s)

    The nodes function return slices of the XML document, onc row per the node you navigated to. The row has a single column, which you cannot access directly, but you must apply some xml method to it. The simplest is query('.') which returns the full contents of the node, so here you get back:

    <Second attr="value"><Third attr="one third">First element in a third</Third><Third attr="two thirds">Second element in a third</Third><Third attr="One">Third element in a third</Third></Second>
    <Second attr="second value"><Third attr="another third">Fourth element in a third</Third><Third attr="yet another third">Fifth element in a third</Third><Third attr="Two">Sixth element in a third</Third></Second>

    To be able to use nodes(), you must an alias for the table and a name for the column. The table alias S is just like C and O in the first example in this post. Since the column returned does not have a name, you also need to define a name for the column, which is the (s) part. This is not peculiar to nodes; the query above could be written as:

       SELECT C.CustomerName, O.IDOrder, O.DateOrder, O.IDEmployee
       FROM   Customers C
       CROSS  APPLY (SELECT TOP 1 O.OrderID, O.OrderDate, O.EmployeeID
                     FROM   Orders O
                     WHERE  O.CustomerID = C.CustomerID
                     ORDER  BY O.OrderID DESC) AS O (IDOrder, DateOrder, IDEmployee)

    But you don't see that very often.

    As for '.' and self::node(), they are two names for the same thing and you should not think too much about it. They both refer to the node you have navgiated to.

    In practice, you use .value to extract values. Here is an example that gives the basic pattern:

    SELECT S.s.value('@attr', 'varchar(30)') AS SecondAttr,
           T.t.value('@attr', 'varchar(30)') AS ThirdAttr,
           T.t.value('(./text())[1]', 'varchar(30)') AS ThirdElem
    FROM   @x.nodes('/First/Second') AS S(s)
    CROSS  APPLY S.s.nodes('Third') AS T(t)

    The attributes are simple: you put an @ before them and off you go. The elements are more difficult as you can see. You cannot simply use '.', although this seems logical. While there only can be a single attribute per node, there can be multiple elements, why you must say which one. And in the very most cases this is the first (and only), whence the [1]. If you try '.[1]' you will get a result, so why the (./text())[1] thing? To explain that fully, I would have to show some degenerate XML documents, but I will save you from that. What is important to know is that the query plan is more efficient with ./text() and this matters for large XML documents, so always use this long-winded notation.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, July 26, 2015 9:23 AM
  • Eric,

    Can you help me with one more related SQL Query Selecting the Connection String again, but this time with a SSRS XML file?

    I have already taken a serious stab at this learning from what you taught me with the first example. My 1st try to do this on my own looked like this below:

    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' AS RS)
     SELECT CP.cp.value('ConnectionString', 'nvarchar(150)') AS ConnectionString
     FROM   @ReportCode t
     CROSS APPLY t.CurrentReportXML.nodes('/Report/DataSources/') AS DSS(dss)
     CROSS APPLY DSS.dss.nodes('/DataSource/') AS DS(ds)
     CROSS APPLY DS.ds.nodes('/ConnectionProperties/') AS CP(cp)

    This is the error I get? Why am I getting and <eof> and what does this mean has happened?

    Msg 9341, Level 16, State 1, Procedure GetDataSourceFromSSRS, Line 28

    XQuery [@ReportCode.CurrentReportXML.nodes()]: Syntax error near '<eof>', expected a step expression.

    I have my sample SSRS XML file loaded at the URL Below:

    http://www.aimreportwriting.com/samplexml/SampleSSRSXML.xml


    Ryan P. Casey • <a href="http://www.R-P-C-Group.com">www.R-P-C-Group.com</a>

    Monday, July 27, 2015 9:12 PM
  • Also, I noticed that the actual raw SSRS file XML has 2 XMLNAMESPACES. I did not use the rd: namespace since I did not select any of the nodes or properties defined by the rd: namespace.

    Is this okay, or do I need to indicate both, all namespaces?


    Ryan P. Casey • <a href="http://www.R-P-C-Group.com">www.R-P-C-Group.com</a>

    Monday, July 27, 2015 9:18 PM
  • Eric,

    Eric Zhang often posts helpful answers here, but since he has not been active in this thread, I'll step in.

    CROSS APPLY t.CurrentReportXML.nodes('/Report/DataSources/') AS DSS(dss)
     CROSS APPLY DSS.dss.nodes('/DataSource/') AS DS(ds)
     CROSS APPLY DS.ds.nodes('/ConnectionProperties/') AS CP(cp)
    ...
    Msg 9341, Level 16, State 1, Procedure GetDataSourceFromSSRS, Line 28

    XQuery [@ReportCode.CurrentReportXML.nodes()]: Syntax error near '<eof>', expected a step expression.

    This error has nothing to do with the contents of the XML document, but it is simply that the argument to nodes that is incorrect. You should not have a trailing slash. XQuery expects something to come after the slash, whence the error.

    Also, for the lower .nodes, you should not have a leading slash. Thus, you should have:

     CROSS APPLY t.CurrentReportXML.nodes('/Report/DataSources') AS DSS(dss)
     CROSS APPLY DSS.dss.nodes('DataSource') AS DS(ds)
     CROSS APPLY DS.ds.nodes('ConnectionProperties') AS CP(cp)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, July 27, 2015 9:34 PM
  • I am so sorry, I got names confused, I meant Erland, not Eric.

    Ok, I fixed what you asked me to, but I am getting a different error now.

    Msg 2389, Level 16, State 1, Procedure GetDataSourceFromSSRS, Line 26

    XQuery [@ReportCode.CurrentReportXML.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' AS RS)
     SELECT CP.cp.value('ConnectionString', 'nvarchar(150)') AS ConnectionString
     FROM   @ReportCode t
     CROSS APPLY t.CurrentReportXML.nodes('/Report/DataSources') AS DSS(dss)
     CROSS APPLY DSS.dss.nodes('/DataSource') AS DS(ds)
     CROSS APPLY DS.ds.nodes('/ConnectionProperties') AS CP(cp)


    Ryan P. Casey • <a href="http://www.R-P-C-Group.com">www.R-P-C-Group.com</a>

    Monday, July 27, 2015 9:42 PM
  • XQuery [@ReportCode.CurrentReportXML.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

    Yes, that is what I discussed in my previous post. To refer to an element, you need to index it, since there can be multiple instances of it:

    DECLARE @x xml =
    '<First>
       <Second attr="value">
          <Third attr="one third">First element in a third</Third>
          <Third attr="two thirds">Second element in a third</Third>
          <Third attr="One">Third element in a third</Third>
       </Second>
       <Second attr="second value">
          <Third attr="another third">Fourth element in a third</Third>
          <Third attr="yet another third">Fifth element in a third</Third>
          <Third attr="Two">Sixth element in a third</Third>
       </Second>
    </First>'

    SELECT S.s.value('Third[3]', 'varchar(30)') AS x
    FROM   @x.nodes('/First/Second') AS S(s)

    And as I noted, make it a habit to always use ./text().


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, July 27, 2015 9:47 PM
  • Ok, I remember you telling me that earlier.

    I guess I am not understanding.

    I understand that Third is an element, so I need to index it to specify that index location like in an array, correct?

    So, if I want all of the elements of Third, do I use ./Text()?

    I am unclear what ./Text() means and when to use it...


    Ryan P. Casey • <a href="http://www.R-P-C-Group.com">www.R-P-C-Group.com</a>

    Monday, July 27, 2015 9:53 PM
  • Ok, I got rid of the error, but I do not get a connection string...

    Have I made a mistake in my Cross Apply Structure matching the raw SSRS XML file structure?

    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' AS RS)
     SELECT CP.cp.value('ConnectionString[1]', 'nvarchar(150)') AS ConnectionString
     FROM   @ReportCode t
     CROSS APPLY t.CurrentReportXML.nodes('/Report/DataSources') AS DSS(dss)
     CROSS APPLY DSS.dss.nodes('/DataSource') AS DS(ds)
     CROSS APPLY DS.ds.nodes('/ConnectionProperties') AS CP(cp)


    Ryan P. Casey • <a href="http://www.R-P-C-Group.com">www.R-P-C-Group.com</a>

    Monday, July 27, 2015 9:59 PM
  • I have tried all different way to get the connection string, but I am always getting no value in every case I show below:

    Have I made a mistake in my Cross Apply Structure matching the raw SSRS XML file structure?

    I am unclear what ./Text() means and when to use it?

    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' AS RS)
     SELECT CP.cp.value('ConnectionString[1]', 'nvarchar(150)') AS ConnectionString
     FROM   @ReportCode t
     CROSS APPLY t.CurrentReportXML.nodes('/Report/DataSources/DataSource/ConnectionProperties') AS CP(cp)
    
    --;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' AS RS)
    -- SELECT CP.cp.query('ConnectionString[1]') AS ConnectionString
    -- FROM   @ReportCode t
    -- CROSS APPLY t.CurrentReportXML.nodes('/Report/DataSources/DataSource/ConnectionProperties') AS CP(cp)
    
     ----;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' AS RS)
    ---- SELECT CP.cp.value('ConnectionString[1]', 'nvarchar(150)') AS ConnectionString
    ---- FROM   @ReportCode t
    ---- CROSS APPLY t.CurrentReportXML.nodes('/Report/DataSources') AS DSS(dss)
    ---- CROSS APPLY DSS.dss.nodes('/DataSource') AS DS(ds)
    ---- CROSS APPLY DS.ds.nodes('/ConnectionProperties') AS CP(cp)
    
    ----;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' AS RS)
    ---- SELECT CP.cp.value('(./text())[1]', 'nvarchar(150)') AS ConnectionString
    ---- FROM   @ReportCode t
    ---- CROSS APPLY t.CurrentReportXML.nodes('/Report/DataSources') AS DSS(dss)
    ---- CROSS APPLY DSS.dss.nodes('/DataSource') AS DS(ds)
    ---- CROSS APPLY DS.ds.nodes('/ConnectionProperties') AS CP(cp)


    Ryan P. Casey • <a href="http://www.R-P-C-Group.com">www.R-P-C-Group.com</a>

    Monday, July 27, 2015 11:40 PM
  • I am still having a hard time getting my SQL in my Cross Apply and Select to work right with an SSRS XML... please help.

    ;WITH XMLNAMESPACES (DEFAULT	'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource',
    								'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd)
    SELECT 
    D.g.value('@Name', 'nvarchar(150)') AS TEst
    
    --C.s.value('./text()[1]', 'nvarchar(150)') AS ConnectionString
    FROM  @CurrentReportXML.nodes('/Report') AS R(t)
    CROSS APPLY R.t.nodes('/Datasources/Datasource') AS D(g) 
    CROSS APPLY D.g.nodes('/ConnectionProperties/ConnectionString') AS C(s) 


    Ryan P. Casey • <a href="http://www.R-P-C-Group.com">www.R-P-C-Group.com</a>

    Tuesday, July 28, 2015 7:50 PM
  • Here is my latest SQL and I have a new thread for this issue at:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e2bcb47b-9d93-4f9a-b8aa-0b313abe9039/help-reading-all-connection-strings-from-an-ssrs-xml-file?forum=sqlxml

    Have I made a mistake in my Cross Apply Structure matching the raw SSRS XML file structure?

    I am unclear what ./Text() means and when to use it?

    I have my sample SSRS XML file loaded at the URL Below:

    http://www.aimreportwriting.com/samplexml/SampleSSRSXML.xml

    ;WITH XMLNAMESPACES (DEFAULT	'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource',
    								'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd)
    SELECT CP.p.value('ConnectionString[1]', 'nvarchar(150)') AS ConnectionString
    FROM   @ReportCode t
    CROSS APPLY t.CurrentReportXML.nodes('/Report/DataSources') AS DSS(d)
    CROSS APPLY DSS.d.nodes('DataSource') AS DS(s)
    CROSS APPLY DS.s.nodes('ConnectionProperties') AS CP(p)


    Ryan P. Casey • <a href="http://www.R-P-C-Group.com">www.R-P-C-Group.com</a>

    Tuesday, July 28, 2015 8:03 PM
  • So, if I want all of the elements of Third, do I use ./Text()?

    I am unclear what ./Text() means and when to use it...

    You should alwayus use /text() when you access elements for better performance.

    Here is an example where it is also make a functional difference:

    DECLARE @x xml =
    '<First>
       <Second attr="value">
          <Third attr="one third">First element in a third</Third>
          <Third attr="two thirds">Second element in a third</Third>
          <Third attr="One">Third element in a third</Third>
       </Second>
       <Second attr="second value">
          <Third attr="another third">Fourth element in a third</Third>
          <Third attr="yet another third">Fifth element in a third</Third>
          <Third attr="Two">Sixth element<A inter="lude"/> in a third</Third>
       </Second>
    </First>'

    SELECT S.s.value('Third[3]', 'varchar(30)') AS x1,
           S.s.value('(Third/text())[3]', 'varchar(30)') AS x2,
           S.s.value('(Third/text())[4]', 'varchar(30)') AS x3
    FROM   @x.nodes('/First/Second') AS S(s)

    But please forget that you ever saw this, because this is just silly.

    Anyway, in you SSRS example. the problem is that you need to prefix the the nodes with the prefix you defined in the WITH XMLNAMESPACES clause. And, not to be ignored: you need to use ConnectString and not ConnectionString. :-)

    DECLARE @x xml =
    '<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
    <Body>
    <ReportItems>
    <Textbox Name="textbox1">
    <CanGrow>true</CanGrow>
    <KeepTogether>true</KeepTogether>
    <Paragraphs>
    <Paragraph>
    <TextRuns>
    <TextRun>
    <Value>Report1</Value>
    <Style>
    <FontFamily>Tahoma</FontFamily>
    <FontSize>20pt</FontSize>
    <FontWeight>Bold</FontWeight>
    <Color>SteelBlue</Color>
    </Style>
    </TextRun>
    </TextRuns>
    <Style/>
    </Paragraph>
    </Paragraphs>
    <rd:DefaultName>textbox1</rd:DefaultName>
    <Height>0.36in</Height>
    <Width>5in</Width>
    <Style>
    <PaddingLeft>2pt</PaddingLeft>
    <PaddingRight>2pt</PaddingRight>
    <PaddingTop>2pt</PaddingTop>
    <PaddingBottom>2pt</PaddingBottom>
    </Style>
    </Textbox>
    <Tablix Name="table1">
    <TablixBody>
    <TablixColumns>
    <TablixColumn>
    <Width>1in</Width>
    </TablixColumn>
    <TablixColumn>
    <Width>1in</Width>
    </TablixColumn>
    </TablixColumns>
    <TablixRows>
    <TablixRow>
    <Height>0.22in</Height>
    <TablixCells>
    <TablixCell>
    <CellContents>
    <Textbox Name="textbox2">
    <CanGrow>true</CanGrow>
    <KeepTogether>true</KeepTogether>
    <Paragraphs>
    <Paragraph>
    <TextRuns>
    <TextRun>
    <Value>Number</Value>
    <Style>
    <FontFamily>Tahoma</FontFamily>
    <FontSize>11pt</FontSize>
    <FontWeight>Bold</FontWeight>
    <Color>White</Color>
    </Style>
    </TextRun>
    </TextRuns>
    <Style>
    <TextAlign>Right</TextAlign>
    </Style>
    </Paragraph>
    </Paragraphs>
    <rd:DefaultName>textbox2</rd:DefaultName>
    <Style>
    <Border>
    <Color>LightGrey</Color>
    <Style>Solid</Style>
    </Border>
    <BackgroundColor>SteelBlue</BackgroundColor>
    <PaddingLeft>2pt</PaddingLeft>
    <PaddingRight>2pt</PaddingRight>
    <PaddingTop>2pt</PaddingTop>
    <PaddingBottom>2pt</PaddingBottom>
    </Style>
    </Textbox>
    </CellContents>
    </TablixCell>
    <TablixCell>
    <CellContents>
    <Textbox Name="textbox3">
    <CanGrow>true</CanGrow>
    <KeepTogether>true</KeepTogether>
    <Paragraphs>
    <Paragraph>
    <TextRuns>
    <TextRun>
    <Value>Event Label</Value>
    <Style>
    <FontFamily>Tahoma</FontFamily>
    <FontSize>11pt</FontSize>
    <FontWeight>Bold</FontWeight>
    <Color>White</Color>
    </Style>
    </TextRun>
    </TextRuns>
    <Style/>
    </Paragraph>
    </Paragraphs>
    <rd:DefaultName>textbox3</rd:DefaultName>
    <Style>
    <Border>
    <Color>LightGrey</Color>
    <Style>Solid</Style>
    </Border>
    <BackgroundColor>SteelBlue</BackgroundColor>
    <PaddingLeft>2pt</PaddingLeft>
    <PaddingRight>2pt</PaddingRight>
    <PaddingTop>2pt</PaddingTop>
    <PaddingBottom>2pt</PaddingBottom>
    </Style>
    </Textbox>
    </CellContents>
    </TablixCell>
    </TablixCells>
    </TablixRow>
    <TablixRow>
    <Height>0.21in</Height>
    <TablixCells>
    <TablixCell>
    <CellContents>
    <Textbox Name="Number">
    <CanGrow>true</CanGrow>

    <KeepTogether>true</KeepTogether>
    <Paragraphs>
    <Paragraph>
    <TextRuns>
    <TextRun>
    <Value>=Fields!Number.Value</Value>
    <Style>
    <FontFamily>Tahoma</FontFamily>
    </Style>
    </TextRun>
    </TextRuns>
    <Style/>
    </Paragraph>
    </Paragraphs>
    <rd:DefaultName>Number</rd:DefaultName>
    <Style>
    <Border>
    <Color>LightGrey</Color>
    <Style>Solid</Style>
    </Border>
    <PaddingLeft>2pt</PaddingLeft>
    <PaddingRight>2pt</PaddingRight>
    <PaddingTop>2pt</PaddingTop>
    <PaddingBottom>2pt</PaddingBottom>
    </Style>
    </Textbox>
    </CellContents>
    </TablixCell>
    <TablixCell>
    <CellContents>
    <Textbox Name="Event_Label">
    <CanGrow>true</CanGrow>
    <KeepTogether>true</KeepTogether>
    <Paragraphs>
    <Paragraph>
    <TextRuns>
    <TextRun>
    <Value>=Fields!Event_Label.Value</Value>
    <Style>
    <FontFamily>Tahoma</FontFamily>
    </Style>
    </TextRun>
    </TextRuns>
    <Style/>
    </Paragraph>
    </Paragraphs>
    <rd:DefaultName>Event_Label</rd:DefaultName>
    <Style>
    <Border>
    <Color>LightGrey</Color>
    <Style>Solid</Style>
    </Border>
    <PaddingLeft>2pt</PaddingLeft>
    <PaddingRight>2pt</PaddingRight>
    <PaddingTop>2pt</PaddingTop>
    <PaddingBottom>2pt</PaddingBottom>
    </Style>
    </Textbox>
    </CellContents>
    </TablixCell>
    </TablixCells>
    </TablixRow>
    </TablixRows>
    </TablixBody>
    <TablixColumnHierarchy>
    <TablixMembers>
    <TablixMember/>
    <TablixMember/>
    </TablixMembers>
    </TablixColumnHierarchy>
    <TablixRowHierarchy>
    <TablixMembers>
    <TablixMember>
    <KeepWithGroup>After</KeepWithGroup>
    <RepeatOnNewPage>true</RepeatOnNewPage>
    <KeepTogether>true</KeepTogether>
    </TablixMember>
    <TablixMember>
    <Group Name="table1_Details_Group">
    <DataElementName>Detail</DataElementName>
    </Group>
    <TablixMembers>
    <TablixMember/>
    </TablixMembers>
    <DataElementName>Detail_Collection</DataElementName>
    <DataElementOutput>Output</DataElementOutput>
    <KeepTogether>true</KeepTogether>
    </TablixMember>
    </TablixMembers>
    </TablixRowHierarchy>
    <DataSetName>DataSet1</DataSetName>
    <Top>0.36in</Top>
    <Height>0.43in</Height>
    <Width>2in</Width>
    <ZIndex>1</ZIndex>
    <Style/>
    </Tablix>
    </ReportItems>
    <Height>0.79in</Height>
    <Style/>
    </Body>
    <Width>5in</Width>
    <Page>
    <LeftMargin>1in</LeftMargin>
    <RightMargin>1in</RightMargin>
    <TopMargin>1in</TopMargin>
    <BottomMargin>1in</BottomMargin>
    <Style/>
    </Page>
    <AutoRefresh>0</AutoRefresh>
    <DataSources>
    <DataSource Name="DataSource1">
    <ConnectionProperties>
    <DataProvider>SQL</DataProvider>
    <ConnectString>
    Data Source=DCORECSI3DB01;Initial Catalog=COMSOL_DW
    </ConnectString>
    <IntegratedSecurity>true</IntegratedSecurity>
    </ConnectionProperties>
    <rd:SecurityType>Integrated</rd:SecurityType>
    <rd:DataSourceID>ab3f44f2-15c7-4516-9828-735f81667697</rd:DataSourceID>
    </DataSource>
    </DataSources>
    <DataSets>
    <DataSet Name="DataSet1">
    <Query>
    <DataSourceName>DataSource1</DataSourceName>
    <CommandText>
    SELECT DIM_SegmentType.* FROM DIM_SegmentType
    </CommandText>
    </Query>
    <Fields>
    <Field Name="Number">
    <DataField>Number</DataField>
    <rd:TypeName>System.Int32</rd:TypeName>
    </Field>
    <Field Name="Event_Label">
    <DataField>Event Label</DataField>
    <rd:TypeName>System.String</rd:TypeName>
    </Field>
    </Fields>
    </DataSet>
    </DataSets>
    <Language>en-US</Language>
    <ConsumeContainerWhitespace>true</ConsumeContainerWhitespace>
    <rd:ReportUnitType>Inch</rd:ReportUnitType>
    <rd:ReportID>cf45e095-2257-4947-ab83-fb5fc07af966</rd:ReportID>
    </Report>'

    SELECT @x
    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' AS RS)
     SELECT CP.cp.value('(RS:ConnectString/text())[1]', 'nvarchar(150)') AS ConnectionString
     FROM   @x.nodes('/RS:Report/RS:DataSources') AS DSS(dss)
     CROSS APPLY DSS.dss.nodes('RS:DataSource') AS DS(ds)
     CROSS APPLY DS.ds.nodes('RS:ConnectionProperties') AS CP(cp)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, July 28, 2015 9:28 PM