none
Help Reading All Connection Strings from an SSRS XML file RRS feed

  • Question

  • I am trying to get the connection string values in a raw SSRS XML file. The XML for the SSRS rdl is located in a SSRS rdl file named SampleSSRSXML.xml that you can download from the URL below.

    The XML is read into a SQL XML Variable named @ReportCode. I have included my many attempts at writing some SQL XML to return all of the connection strings in the SSRS XML file, but everything I have tried returns a NULL connection string.

    Can someone help me write the correct XML to get all of the XML values for Connection String in the SSRS XML file I provided?

    I have tried many different ways 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?

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

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

    ;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>

    Tuesday, July 28, 2015 1:57 PM

Answers

  • Hi RPCASEY001,

    To extract the ConnectionString from the given XML, you can reference the below sample.

    DECLARE @xml 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>
    '
     
    DECLARE @ReportCode TABLE(CurrentReportXML XML)
    INSERT INTO @ReportCode SELECT @XML
    SELECT * FROM @ReportCode
    
     ;WITH XMLNAMESPACES (DEFAULT	'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition',
    								'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd)
     SELECT CP.cp.value('ConnectString[1]', 'nvarchar(150)') AS ConnectionString
     FROM   @ReportCode t
     CROSS APPLY t.CurrentReportXML.nodes('/Report/DataSources/DataSource/ConnectionProperties') AS CP(cp)
    
      

    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support

    Wednesday, July 29, 2015 8:52 AM
    Moderator

All replies

  • This article might help.

    Andy Tauber
    Data Architect
    The Vancouver Clinic
    Website | LinkedIn

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Tuesday, July 28, 2015 5:40 PM
  • Thanks, but this is reading from the report server database for shared data sources. I am reading the rdl XML and looking for data sources that are not shared.


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

    Tuesday, July 28, 2015 6:59 PM
  • Here is my latest attempt:

    ;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:04 PM
  • Hi RPCASEY001,

    To extract the ConnectionString from the given XML, you can reference the below sample.

    DECLARE @xml 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>
    '
     
    DECLARE @ReportCode TABLE(CurrentReportXML XML)
    INSERT INTO @ReportCode SELECT @XML
    SELECT * FROM @ReportCode
    
     ;WITH XMLNAMESPACES (DEFAULT	'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition',
    								'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd)
     SELECT CP.cp.value('ConnectString[1]', 'nvarchar(150)') AS ConnectionString
     FROM   @ReportCode t
     CROSS APPLY t.CurrentReportXML.nodes('/Report/DataSources/DataSource/ConnectionProperties') AS CP(cp)
    
      

    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support

    Wednesday, July 29, 2015 8:52 AM
    Moderator