xpath下面的xpath_深入研究XPATH查询

最新推荐文章于 2024-05-13 23:48:00 发布

culuo4781

最新推荐文章于 2024-05-13 23:48:00 发布

阅读量502

点赞数

文章标签：数据库 python java sql sqlserver

原文链接：https://www.sqlshack.com/taking-deeper-dive-into-xpath-queries/

版权

xpath下面的xpath

介绍 ( Introduction )

In my last article entitled “Which fields do my reports actually use”, we had a quick look at a practical implementation of an XPATH query to obtain a list of database table fields being utilized by our reports. Reporting Services reports are really no more than XML documents. The fields utilized by our reports reside in Reporting Services datasets.

在上一篇名为“我的报告实际使用哪个字段”的文章中，我们快速查看了XPATH查询的实际实现，以获取报告正在使用的数据库表字段的列表。实际上，Reporting Services报表只不过是XML文档。我们的报告使用的字段位于Reporting Services数据集中。

In that article, the eagle – eyed reader would have noted that only the first field for each dataset was pulled. This was done intentionally and was meant to act as an introduction to utilizing more complex techniques to pull the complete suite of data fields.

在这篇文章中，老鹰-眼的读者会注意到，只有每个数据集的第一个字段被拔掉。这是有意进行的，目的是作为介绍使用更复杂的技术提取完整数据字段集的介绍。

Today’s end goal (to pull all the fields) may be seen in the screen shot shown below:

今天的最终目标（拉动所有领域）可以在以下屏幕截图中看到：

入门 ( Getting Started )

The small snippet of code shown below helps us obtain the names of the “data fields” utilized by our reports that are resident on our report server. It renders the Report Path (where the report is located), the name of the report and the bulk of the data that we need which is found in an image field called “Content” which we convert to XML (see below).

下面显示的一小段代码有助于我们获取驻留在报表服务器上的报表所使用的“数据字段”的名称。它呈现报告路径（报告所在的位置），报告的名称以及我们需要的大量数据，这些信息在名为“ Content”的图像字段中找到，我们将其转换为XML（请参见下文）。

 
SELECT RPT.Path AS ReportPath
                 ,RPT.name AS ReportName
                 ,CONVERT(xml, CONVERT(varbinary(max), RPT.content)) AS contentXML
            FROM [ReportServer].dbo.[Catalog] AS RPT
            INNER JOIN [ReportServer].dbo.ExecutionLogStorage Storage ON RPT.ItemID =
            Storage.ReportID 
            WHERE RPT.Type = 2

Obtaining the names of the dataset/reports fields is slightly more convoluted. In order to obtain this data we need to descend the following path (see the screen shot below).

获取数据集/报告字段的名称有些复杂。为了获得此数据，我们需要沿以下路径前进（请参见下面的屏幕截图）。

/Report/DataSets/DataSet//Fields/Field/DataField

/ Report / DataSets / DataSet // Fields / Field / DataField

Our task is now to ensure that when we run this query, that all the necessary “report fields” are being successfully extracted. As always, the complete code listing may be found in Addenda 1.

现在，我们的任务是确保在运行此查询时，已成功提取所有必要的“报告字段”。与往常一样，完整的代码清单可以在附录1中找到。

One of the nagging issues that I have always encountered is that we are never really 100% confident of the maximum number of fields that a dataset will contain. In most of the dataset that I have utilized, I have found that the number of sibling fields/nodes rarely exceeds thirty fields per dataset.

我一直遇到的棘手问题之一是，我们从来没有真正100％确信数据集将包含的最大字段数。在我使用的大多数数据集中，我发现同级字段/节点的数量很少超过每个数据集三十个字段。

We set our root path to Report/DataSets/DataSet

我们将根路径设置为Report / DataSets / DataSet

Obtaining the report name, the report path and the contentXML fields are fairly simple. They form the first portion of our “Select” statement (see below).

获取报告名称，报告路径和contentXML字段非常简单。它们构成了我们“选择”语句的第一部分（见下文）。

 
;WITH 
 XMLNAMESPACES
     (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition'
             ,'http://schemas.microsoft.com/sqlserver/reporting/reportdesigner'
      AS rd)
,DEF AS
    (SELECT RPT.ReportPath,ReportName,contentXML
           ,Y.RptNode.value('@Name[1]', 'nvarchar(425)') AS DataSourceName 
           ,R.RptNode.value('@Name[1]', 'nvarchar(425)') AS DataSetName
 
………….
FROM (SELECT RPT.Path AS ReportPath
                 ,RPT.name AS ReportName
                 ,CONVERT(xml, CONVERT(varbinary(max), RPT.content)) AS contentXML
           FROM [ReportServer].dbo.[Catalog] AS RPT
            WHERE RPT.Type = 2 
                                ) AS RPT

The code snippet above, shows us the subquery containing these first three fields (i.e. RPT.ReportPath, ReportName, and contextXML) plus two additional fields in blue. The fields in blue are discussed in a few seconds.

上面的代码片断，我们示出了包含第一本身 三个字段子查询（即RPT.ReportPath，REPORTNAME和contextXML）加在蓝色两个附加字段。几秒钟内将讨论蓝色的字段。

This whole subquery may also be altered to ascertain if the report and fields have been utilized in the recent past (see below in bold text).

也可以更改整个子查询，以确定最近是否使用了报告和字段（请参见下面的粗体字）。

 
FROM (SELECT RPT.Path AS ReportPath
                 ,RPT.name AS ReportName
                 ,CONVERT(xml, CONVERT(varbinary(max), RPT.content)) AS contentXML
            FROM [ReportServer].dbo.[Catalog] AS RPT
            INNER JOIN [ReportServer].dbo.ExecutionLogStorage Storage ON RPT.ItemID = Storage.ReportID 
            WHERE RPT.Type = 2

The complete code listing OF THIS QUERY may be found in Addenda 2.

此查询的完整代码列表可在附录2中找到。

When it comes to the DataSourceName and the DataSetName, we must JOIN back to the “Catalog” table (in the Report Server database) utilizing the “Content” field and the XPath statements to the DataSourceName and DataSetNameNodes. When working with XML we utilize a “cross APPLY” instead of an inner join(see below).

当涉及到DataSourceName和DataSetName时，我们必须使用“ Content”字段和对DataSourceName和DataSetNameNodes的XPath语句将JOIN返回到“ Catalog”表（在Report Server数据库中）。使用XML时，我们使用“交叉应用”而不是内部联接（请参见下文）。

 
cross APPLY RPT.contentXML.nodes('/Report/DataSets/DataSet') AS R(RptNode)
cross APPLY RPT.contentXML.nodes('/Report/DataSources/DataSource') AS Y(RptNode).

As mentioned, these cross APPLY’s contain the path to the necessary leaf nodes where we shall be able to find the date source name and the name of the dataset (above and below).

如前所述，这些交叉的APPLY包含指向必要的叶子节点的路径，在这里我们将能够找到日期源名称和数据集的名称（上方和下方）。

 
           ,Y.RptNode.value('@Name[1]', 'nvarchar(425)') AS DataSourceName 
           ,R.RptNode.value('@Name[1]', 'nvarchar(425)') AS DataSetName

获取其余字段 ( Obtaining the remaining fields )

As discussed above, knowing the maximum number of fields that the largest of our report datasets contains, is always a conundrum. The ones that I utilize are normally no more than 30 fields, the reason being that most of my reports contain matrices that show data for twelve months (both actuals and goals). This clearly accounts for twenty four fields. The other six being descriptive.

如上所述，知道最大的报告数据集包含的最大字段数始终是一个难题。我使用的字段通常不超过30个字段，原因是我的大多数报告都包含显示十二个月数据（实际值和目标值）的矩阵。显然，这占了二十四个领域。其他六个是描述性的。

Working from the XPATH expression “R” (shown immediately above), we are now in a position to pull all thirty plus fields within any dataset (see below).

从XPATH表达式“ R”（如上所示）开始，我们现在可以提取任何数据集中的所有三十个加号字段（请参见下文）。

 
REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[1]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields1
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[2]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields2
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[3]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields3

ALL the way through to Field31

一直到Field31

 
REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[30]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')            
            AS Fields30
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[31]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields31

The observant reader will call “foul” as not all datasets will have thirty plus fields.

细心的读者会称其为“犯规”，因为并非所有数据集都具有三十多个字段。

This is true, however those fields that do not exist will have “NULL” for the corresponding “Field” node value (see below).

的确如此，但是那些不存在的字段的相应“字段”节点值将为“ NULL”（请参见下文）。

…and

…和

获取我们的报告字段的最终列表 ( Obtaining our end list of report fields )

Having obtained the name of the report, the data source name, the dataset name and a whole wad of other fields, we must now consolidate all of this into four distinct fields, “Reportname”, “DataSourceName”,”DataSetName” and “Fields1”. “Fields1” will contain the names of the report fields within each report (e.g. Last Name, First Name, City, State, Country etc.).

获得了报告的名称，数据源名称，数据集名称以及其他字段的整体后，我们现在必须将所有这些合并为四个不同的字段，即“ Reportname”，“ DataSourceName”，“ DataSetName”和“ Fields1”。 ”。 “ Fields1”将包含每个报告中报告字段的名称（例如，姓，名，城市，州，国家等）。

We achieve this by creating the following query.

我们通过创建以下查询来实现。

 
select ReportName, DataSourceName, DatasetName,Fields1  into [ServerStatistics].dbo.ReportFields3 from #rawdata1  
union all
select ReportName, DataSourceName, DatasetName,Fields2 from #rawdata1
union all
select ReportName, DataSourceName, DatasetName,Fields3 from #rawdata1	
union all
……………
select ReportName, DataSourceName, DatasetName,Fields29 from #rawdata1	
union all
select ReportName, DataSourceName, DatasetName,Fields30 from #rawdata1
union all
select ReportName, DataSourceName, DatasetName,Fields31 from #rawdata1

Once loaded, we still have one last task to do and that is to remove those records that have null values (see below).

加载后，我们还有最后一个任务要做，那就是删除那些具有空值的记录（见下文）。

Once again, these values are NULL as there were no corresponding fields in the ‘SalesPerson” dataset (as an example) for perhaps “Field21” and “Field22”. The observant reader will note that at this stage the concept of “Fields2” through “Field31” no longer exist due to the “union”. The important point being that these records with NULL (as a value) must be removed from the recordset.

再一次，这些值是NULL因为有在“业务员”数据集也许（作为一个例子）‘Field21’和‘Field22’没有相应的字段。细心的读者会注意到，在此阶段，由于“联合”，“ Fields2”至“ Field31”的概念已不复存在。重要的一点是必须将这些具有NULL（作为值）的记录从记录集中删除。

The said, we now issue the following T-SQL statement:

也就是说，我们现在发出以下T-SQL语句：

 
Delete from [ServerStatistics].dbo.ReportFields3
Where Fields1 is null

买者自负 ( Caveat Emptor )

As with any process, we shall find the normal “gotcha’s”.

与任何过程一样，我们将找到正常的“陷阱”。

When I first attempted this exercise, my hair turned grey as 95% of the XML data would come out and the other 5% was NULL when it should NOT have been. The reason for this is related to the namespaces utilized by the XML.

当我第一次尝试这个练习，我的头发变成了灰色作为XML数据的95％的人会站出来，另5％为空时，它不应该是 恩。 其原因 与 XML使用 的 名称空间 有关。

Indications are that some of the XML data utilized the 2010/01 name spaces and others 2008/01 (see below).

提示某些XML数据使用2010/01命名空间，而其他XML数据则使用2008/01（请参见下文）。

 
;WITH 
 XMLNAMESPACES
     (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition'
             ,'http://schemas.microsoft.com/sqlserver/reporting/reportdesigner'
      AS rd)
,DEF AS
;WITH 
 XMLNAMESPACES
     (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition'
             ,'http://schemas.microsoft.com/sqlserver/reporting/reportdesigner'
      AS rd)
,DEF AS

The screen shot above shows the “Content” field within the Reporting Services Database “Catalog” table, which requires the usage of the 2008 names spaces.

上面的屏幕快照显示了Reporting Services数据库“目录”表中的“内容”字段，该字段要求使用2008年名称空间。

You have been warned!

你被警告了！

结论 ( Conclusions )

When contemplating any database migration, one often tries to perform a bit of housekeeping prior to proceeding with the migration. In a recent migration to the cloud, the client wanted to have an inventory of the datasets and fields that were utilized by their reports and to remove unnecessary reports or unused reports (for that matter). In reality this is a double edged sword. In one case one tends to want to look at recent execution of reports as a guide line to what must be maintained, retained and moved. On the other hand, some reports tend to be seasonal and most enterprises truncate report server logs on a periodic basis; thus if one does not pay attention to this fact, potentially useful and important reports tend to fall through the cracks.

在考虑进行任何数据库迁移时，通常会在进行迁移之前尝试执行一些内务处理。在最近向云的迁移中，客户希望获得其报告所使用的数据集和字段的清单，并删除不必要的报告或未使用的报告（为此）。实际上，这是一把双刃剑。在一种情况下，人们倾向于将最近的报告执行情况视为必须维护，保留和移动的内容的指南。另一方面，某些报告往往是季节性的，并且大多数企业会定期截断报告服务器日志。因此，如果不注意这一事实，潜在的有用和重要的报告往往会落空。

This said, Addenda 1 contains the code to be executed regardless of the last runs of the report.

也就是说，附录1包含要执行的代码，而与报告的最后运行无关。

Addenda 2 (on the other hand) is execution based and will render data based upon execution times from the log table.

附录2（基于另一方面）基于执行，并且将根据日志表中的执行时间来呈现数据。

So we come to the end of another “get together”. As always, should you have any questions or concerns, please feel free to contact me.

因此，我们走到了另一个“聚在一起”的结尾。与往常一样，如果您有任何疑问或疑虑，请随时与我联系。

In the interim, happy programming!

在此期间，编程愉快！

附录1 ( Addenda 1 )

 
IF OBJECT_ID(N'tempdb..#rawdata1') IS NOT NULL
BEGIN
     DROP TABLE #rawdata1
END
 
IF OBJECT_ID(N'tempdb..#rawdata2') IS NOT NULL
BEGIN
     DROP TABLE #rawdata2
END
GO
;WITH 
 XMLNAMESPACES
     (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition'
             ,'http://schemas.microsoft.com/sqlserver/reporting/reportdesigner'
      AS rd)
,DEF AS
    (SELECT RPT.ReportPath,ReportName,contentXML
           ,Y.RptNode.value('@Name[1]', 'nvarchar(425)') AS DataSourceName 
           ,R.RptNode.value('@Name[1]', 'nvarchar(425)') AS DataSetName
	
		   ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[1]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields1
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[2]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields2
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[3]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields3            
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[4]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields4 
		   ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[5]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')            
            AS Fields5
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[6]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields6
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[7]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields7            
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[8]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields8  
		   ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[9]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')            
            AS Fields9
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[10]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields10
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[11]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields11            
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[12]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields12
            
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[13]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')            
            AS Fields13
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[14]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields14
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[15]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields15            
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[16]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields16
   ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[17]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields17            
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[18]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields18  
		   ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[19]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')            
            AS Fields19
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[20]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields20 
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[21]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields21
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[22]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields22            
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[23]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields23
            
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[24]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')            
            AS Fields24
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[25]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields25
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[26]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields26            
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[27]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields27
           ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[28]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields28            
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[29]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields29  
		   ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[30]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')            
            AS Fields30
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[31]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields31                  
         
     FROM (SELECT RPT.Path AS ReportPath
                 ,RPT.name AS ReportName
                 ,CONVERT(xml, CONVERT(varbinary(max), RPT.content)) AS contentXML
           FROM [ReportServer].dbo.[Catalog] AS RPT
            WHERE RPT.Type = 2 
                                ) AS RPT
     cross APPLY RPT.contentXML.nodes('/Report/DataSets/DataSet') AS R(RptNode)
	 cross APPLY RPT.contentXML.nodes('/Report/DataSources/DataSource') AS Y(RptNode)
		    )
SELECT DEF.ReportPath 
,contentXML 
     ,Def.Reportname
     ,DEF.DataSourceName 
      ,DEF.DataSetName
	  ,DEF.Fields1
	  ,DEF.Fields2
	  ,DEF.Fields3
	  ,DEF.Fields4
	  ,DEF.Fields5
	  ,DEF.Fields6
	  ,DEF.Fields7
	  ,DEF.Fields8
	  ,DEF.Fields9
	  ,DEF.Fields10
	  ,DEF.Fields11
	  ,DEF.Fields12
	  ,DEF.Fields13
	  ,DEF.Fields14
	  ,DEF.Fields15
	  ,DEF.Fields16
	  ,DEF.Fields17
	  ,DEF.Fields18
	  ,DEF.Fields19
	  ,DEF.Fields20
	  ,DEF.Fields21
	  ,DEF.Fields22
	  ,DEF.Fields23
	  ,DEF.Fields24
	  ,DEF.Fields25
	  ,DEF.Fields26
	  ,DEF.Fields27
	  ,DEF.Fields28
	  ,DEF.Fields29
	  ,DEF.Fields30
	  ,DEF.Fields31
	   into #rawdata1
FROM DEF
 		go
 		
		select ReportName, DataSourceName, DatasetName,Fields1  into [ServerStatistics].dbo.ReportFields3 from #rawdata1  
		union all
		select ReportName, DataSourceName, DatasetName,Fields2 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields3 from #rawdata1	
		union all
		select ReportName, DataSourceName, DatasetName,Fields4 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields5 from #rawdata1	
		union all
		select ReportName, DataSourceName, DatasetName,Fields6 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields7 from #rawdata1	
		union all
		select ReportName, DataSourceName, DatasetName,Fields8 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields9 from #rawdata1	
		union all
		select ReportName, DataSourceName, DatasetName,Fields10 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields11 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields12 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields13 from #rawdata1	
		union all
		select ReportName, DataSourceName, DatasetName,Fields14 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields15 from #rawdata1	
		union all
		select ReportName, DataSourceName, DatasetName,Fields16 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields17 from #rawdata1	
		union all
		select ReportName, DataSourceName, DatasetName,Fields18 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields19 from #rawdata1	
		union all
		select ReportName, DataSourceName, DatasetName,Fields20 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields21 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields22 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields23 from #rawdata1	
		union all
		select ReportName, DataSourceName, DatasetName,Fields24 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields25 from #rawdata1	
		union all
		select ReportName, DataSourceName, DatasetName,Fields26 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields27 from #rawdata1	
		union all
		select ReportName, DataSourceName, DatasetName,Fields28 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields29 from #rawdata1	
		union all
		select ReportName, DataSourceName, DatasetName,Fields30 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields31 from #rawdata1			
	 GO
-- Remove the NULL records from the table. That is, where Fields1 is NULL as there are NO        
--values for these nodes.	
		
Delete from [ServerStatistics].dbo.ReportFields3
Where Fields1 is null

附录2 ( Addenda 2 )

Code to report fields based upon recent usage

用于根据最近的使用情况报告字段的代码

 
IF OBJECT_ID(N'tempdb..#rawdata1') IS NOT NULL
BEGIN
     DROP TABLE #rawdata1
END
 
IF OBJECT_ID(N'tempdb..#rawdata2') IS NOT NULL
BEGIN
     DROP TABLE #rawdata2
END
GO
;WITH 
 XMLNAMESPACES
     (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition'
             ,'http://schemas.microsoft.com/sqlserver/reporting/reportdesigner'
      AS rd)
,DEF AS
    (SELECT RPT.ReportPath,ReportName,contentXML
           ,Y.RptNode.value('@Name[1]', 'nvarchar(425)') AS DataSourceName 
           ,R.RptNode.value('@Name[1]', 'nvarchar(425)') AS DataSetName
 
		   ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[1]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields1
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[2]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields2
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[3]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields3            
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[4]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields4 
		   ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[5]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')            
            AS Fields5
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[6]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields6
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[7]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields7            
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[8]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields8  
		   ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[9]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')            
            AS Fields9
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[10]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields10
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[11]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields11            
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[12]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields12
            
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[13]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')            
            AS Fields13
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[14]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields14
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[15]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields15            
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[16]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields16
   ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[17]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields17            
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[18]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields18  
		   ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[19]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')            
            AS Fields19
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[20]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields20 
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[21]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields21
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[22]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields22            
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[23]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields23
            
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[24]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')            
            AS Fields24
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[25]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields25
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[26]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields26            
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[27]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields27
           ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[28]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields28            
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[29]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields29  
		   ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[30]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')            
            AS Fields30
            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(.//Fields/Field/DataField)[31]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields31  
         
     FROM (SELECT RPT.Path AS ReportPath
                 ,RPT.name AS ReportName
                 ,CONVERT(xml, CONVERT(varbinary(max), RPT.content)) AS contentXML
            FROM [ReportServer].dbo.[Catalog] AS RPT
            INNER JOIN [ReportServer].dbo.ExecutionLogStorage Storage ON RPT.ItemID =
            Storage.ReportID 
            WHERE RPT.Type = 2 
                                ) AS RPT
     cross APPLY RPT.contentXML.nodes('/Report/DataSets/DataSet') AS R(RptNode)
	 cross APPLY RPT.contentXML.nodes('/Report/DataSources/DataSource') AS Y(RptNode)
		    )
SELECT DEF.ReportPath 
,contentXML 
     ,Def.Reportname
     ,DEF.DataSourceName 
      ,DEF.DataSetName
	  ,DEF.Fields1
	  ,DEF.Fields2
	  ,DEF.Fields3
	  ,DEF.Fields4
	  ,DEF.Fields5
	  ,DEF.Fields6
	  ,DEF.Fields7
	  ,DEF.Fields8
	  ,DEF.Fields9
	  ,DEF.Fields10
	  ,DEF.Fields11
	  ,DEF.Fields12
	  ,DEF.Fields13
	  ,DEF.Fields14
	  ,DEF.Fields15
	  ,DEF.Fields16
	  ,DEF.Fields17
	  ,DEF.Fields18
	  ,DEF.Fields19
	  ,DEF.Fields20
	  ,DEF.Fields21
	  ,DEF.Fields22
	  ,DEF.Fields23
	  ,DEF.Fields24
	  ,DEF.Fields25
	  ,DEF.Fields26
	  ,DEF.Fields27
	  ,DEF.Fields28
	  ,DEF.Fields29
	  ,DEF.Fields30
	  ,DEF.Fields31
	 
	  into #rawdata1
FROM DEF
 
		go
		select ReportName, DataSourceName, DatasetName,Fields1  into [ServerStatistics].dbo.ReportFieldsWith3MonthHistory from #rawdata1  
		union all
		select ReportName, DataSourceName, DatasetName,Fields2 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields3 from #rawdata1	
		union all
		select ReportName, DataSourceName, DatasetName,Fields4 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields5 from #rawdata1	
		union all
		select ReportName, DataSourceName, DatasetName,Fields6 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields7 from #rawdata1	
		union all
		select ReportName, DataSourceName, DatasetName,Fields8 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields9 from #rawdata1	
		union all
		select ReportName, DataSourceName, DatasetName,Fields10 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields11 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields12 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields13 from #rawdata1	
		union all
		select ReportName, DataSourceName, DatasetName,Fields14 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields15 from #rawdata1	
		union all
		select ReportName, DataSourceName, DatasetName,Fields16 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields17 from #rawdata1	
		union all
		select ReportName, DataSourceName, DatasetName,Fields18 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields19 from #rawdata1	
		union all
		select ReportName, DataSourceName, DatasetName,Fields20 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields21 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields22 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields23 from #rawdata1	
		union all
		select ReportName, DataSourceName, DatasetName,Fields24 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields25 from #rawdata1	
		union all
		select ReportName, DataSourceName, DatasetName,Fields26 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields27 from #rawdata1	
		union all
		select ReportName, DataSourceName, DatasetName,Fields28 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields29 from #rawdata1	
		union all
		select ReportName, DataSourceName, DatasetName,Fields30 from #rawdata1
		union all
		select ReportName, DataSourceName, DatasetName,Fields31 from #rawdata1			
	 
	 delete from [ServerStatistics].dbo.ReportFieldsWith3MonthHistory
	 where Fields1 is null

翻译自: https://www.sqlshack.com/taking-deeper-dive-into-xpath-queries/

xpath下面的xpath

culuo4781

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
xpath下面的xpath_深入研究XPATH查询

xpath下面的xpath 介绍 (Introduction) In my last article entitled “Which fields do my reports actually use”, we had a quick look at a practical implementation of an XPATH query to obtain a list of d...
复制链接

扫一扫