1

Topic: Methods value and nodes

Good afternoon!
Respected , you could not help with the decision of the following task?
There is a column in a format image, after coercion in type ml try to implement the following code:
WITH XMLNAMESPACES (
DEFAULT ' http://schemas.microsoft.com/sqlserver/ … definition ';
' http://schemas.microsoft.com/sqlserver/ … definition ' AS REP
)
SELECT C.ItemID;
C.Name;
C.Path;
DataSetXML.value (' @Name ', ' varchar (MAX) ') DataSourceName;
DataSetXML.value (' REP:Query [1]/REP:CommandText [1] ', ' varchar (MAX) ') CommandText;
ReportXML
FROM (SELECT ItemID;
CAST (CAST (CONTENT AS VARBINARY (MAX)) AS XML) ReportXML
FROM [dbo]. [Catalog]
WHERE [Content] IS NOT NULL) ReportXML
OUTER APPLY ReportXML.nodes ('//REP:DataSet ') DataSetXML (DataSetXML)
LEFT JOIN [dbo]. [Catalog] c
ON ReportXML.ItemID = c. ItemID
WHERE C.Name LIKE ' % %
No partition of lines happens and in 4 and 5 column fall out value NULL.

2

Re: Methods value and nodes

More truly, happens so: in a part the partition all the same transits a sink, in a part is not present.

3

Re: Methods value and nodes

Here so earned
SELECT distinct SUBSTRING (
X. CatContent
,X. CIndex
,CHARINDEX (' "' X. CatContent X. CIndex+7) X. CIndex
)
FROM
(
SELECT CatContent = CONVERT (NVARCHAR (MAX), CONVERT (XML, CONVERT (VARBINARY (MAX), C.Content)))
,CIndex = CHARINDEX (' xmlns = "', CONVERT (NVARCHAR (MAX), CONVERT (XML, CONVERT (VARBINARY (MAX), C.Content))))
FROM dbo. Catalog A C
WHERE C.Content is not null
AND C.Type = 2
) X
;
WITH XMLNAMESPACES (DEFAULT ' http://schemas.microsoft.com/sqlserver/ … definition ', ' http://schemas.microsoft.com/SQLServer/ … rtdesigner ' AS rd)
SELECT ReportName = name
,DataSourceName = x.value (' (Query/DataSourceName) [1] ', ' VARCHAR (250) ')
,CommandText = x.value (' (Query/CommandText) [1] ', ' VARCHAR (250) ')
,path
FROM (SELECT C.Name, c.path, CONVERT (XML, CONVERT (VARBINARY (MAX), C.Content)) AS reportXML
FROM dbo. Catalog A C
WHERE C.Content is not null
AND C.Type = 2
) a
outer APPLY reportXML.nodes ('/Report/DataSets/DataSet ') r (x)
ORDER BY name