MS SQL URL Encoded XML Reading Child Node-CodePudding

i need some help for reading the value of a node xml in ms sql.

The table looks like

In the example the select schould give

4 times fpid = 142 and fid = 5 with value like(see above) maybe in the xml column settings of fpid = 26 and fid = 5 there are 2 values in "value" in fpid = 177 and fid = 5 there is only one value in "value" in the xml column

and so on.

CodePudding user response：

Even though the question is already 1 year and 5 months old, I would like to share my findings. The difficulty is that the DBNAME nodes can be at different levels. I took the approach of looking for them in the EXTENDED STATEMENT regardless of their level and then fetching the then associated information such as OPERATION and VALUE in addition in a recursive manner.

Additionally one must consider the NAMESPACES with the EXTENDED STATEMENT. Here I used the Asterisk (example : *:Operation) parameter, because I saw no other possibility than to circumvent the namespaces with this. But then also WITH XMLNAMESPACES (...) is not really necessary - therefore i have commented it in the code.

(It is important to know that IF you use the XMLNAMESPACES, that you DON'T forget any namespace!).

Interestingly, I was looking for this solution myself for this "Docuware specific" problem and therefore ended up exactly with your question.

    --with xmlnamespaces (
    --  'http://www.w3.org/2001/XMLSchema' as xsd,
    --  'http://www.w3.org/2001/XMLSchema-instance' as xsi,
    --  'http://dev.docuware.com/settings/web/querybuilder' as qb
    --)
    select A.fpid as PROFILE_ID
    ,A.fid as FILECABINET_ID
    ,A.name as PROFILE_NAME
    ,cast(ExtendedStatement.value('(./text())[1]', 'nvarchar(max)') as xml) as ESXML
    ,x.y.value('(../../@*:type)[1]', 'nvarchar(max)') as Conditions_type
    ,x.y.value('(../../*:Operation)[1]', 'nvarchar(max)') as Conditions_Operation
    ,x.y.value('(text())[1]', 'nvarchar(max)') as DBName
    ,x.y.value('(../*:Provider/*:Operation)[1]', 'nvarchar(max)') as Provider_Operation
    ,x.y.value('(../*:Provider/*:Value)[1]', 'nvarchar(max)') as Provider_Value
    ,x.y.value('(../*:Provider/*:AddWildcard)[1]', 'nvarchar(max)') as Provider_AddWildcard
    ,x.y.value('(../*:Provider/*:SystemFunction)[1]', 'nvarchar(max)') as Provider_SystemFunction
    ,x.y.value('(../*:Provider/@*:type)[1]', 'nvarchar(max)') as Provider_Type
    FROM [dwsystem].[dbo].[DWFCProfile] A
    OUTER APPLY A.settings.nodes('/FCProfile/IndexFilters/SearchFilter/ExtendedStatement') as SearchFilter(ExtendedStatement)
    outer apply (
    select cast(ExtendedStatement.value('(./text())[1]', 'nvarchar(max)') as xml) as EmbeddedXML
    ) Embedded
    outer apply EmbeddedXML.nodes('//*:DBName') as x(y)
    where A.type = 'DocuWare.Settings.FCProfiles.FCProfile, DocuWare.Settings'

Example Result

CodePudding user response：

Well, you seem to have two problems here:

Embedded XML, and
Embedded XML with namespaces.

The elements you're trying to query in the embedded have a default namespace defined on the QueryBuilder element of http://dev.docuware.com/settings/web/querybuilder, so you'll need to reference that in your XPath query...

with xmlnamespaces (
  'http://dev.docuware.com/settings/web/querybuilder' as qb
)
select Provider.value('(qb:Value/text())[1]', 'nvarchar(max)') as Value
from (
  select cast(ExtendedStatement.value('(./text())[1]', 'nvarchar(max)') as xml) as EmbeddedXML
  from dbo.DWFCProfile
  cross apply settings.nodes('/FCProfile/IndexFilters/SearchFilter/ExtendedStatement') SearchFilter(ExtendedStatement)
  where fid = 5
) Embedded
cross apply EmbeddedXML.nodes('/qb:QueryBuilderSettings/qb:Root/qb:Conditions/qb:Provider') as Conditions(Provider);

Because there are so many left joins involved in your extended query you can use outer apply instead of cross apply to allow rows to be returned even if some values might be null. So that looks like the following (untested, because I don't have your database available)...

with xmlnamespaces (
  'http://dev.docuware.com/settings/web/querybuilder' as qb
)
select
  a.name as Benutzer,
  c.name as Gruppe,
  e.name as Rolle,
  i.name as Archivprofile,
  k.name as Archiv,
  Provider.value('(qb:Value/text())[1]', 'nvarchar(max)') as Value
from dbo.DWUser as a
left join dbo.DWUserToGroup b on b.uid=a.uid
left join dbo.DWGroup c on c.gid=b.gid
left join dbo.DWUserToRole d on d.uid=a.uid
left join dbo.DWGroupToRole j on j.gid=c.gid
left join dbo.DWRoles e on e.rid=j.rid
left join dbo.DWFCProfileToRole h on h.rid=e.rid
left join dbo.DWFCProfile i on i.fpid=h.fpid
left join dbo.DWFileCabinet k on k.fid=i.fid
outer apply i.settings.nodes('/FCProfile/IndexFilters/SearchFilter/ExtendedStatement') SearchFilter(ExtendedStatement)
outer apply (
  select cast(ExtendedStatement.value('(./text())[1]', 'nvarchar(max)') as xml) as EmbeddedXML
) Embedded
outer apply EmbeddedXML.nodes('/qb:QueryBuilderSettings/qb:Root/qb:Conditions/qb:Provider') as Conditions(Provider)
order by k.name;