Parameter to xml value method through a join result

Question

I am shredding an xml column in a view, and extracting information from that column. My view is currently running a large CASE statement on local-name((/*)[1] to get the name of the root node in the XML, and then I am drilling into the xml to fetch a specific value based on the CASE.

E.g.

CASE xdata.value('local-name((/*)[1])', 'sysname')
WHEN 'Person' THEN xdata.value('(/*/PersonGuid/Personnel/@UserName)[1]', 'varchar(50)')
WHEN 'Component' THEN xdata.value('(/*/ComponentGuid/Component/@Number)[1]', 'varchar(50)')
END

This case statement is quite large and unwieldy.

What I have tried to do is build a dictionary in a CTE that contains the item I am evaluating in the CASE, and then the parameter value I would like to supply to the XML value method. E.g. for the example above, the CTE dictionary would contain the following (and the CTE is joined correctly into the main outer query):

;WITH CTE_Selector (TableName, Selector) AS
(
SELECT 'Person', '(/*/PersonGuid/Personnel/@UserName)[1]'
UNION SELECT 'Component' ,'(/*/ComponentGuid/Component/@Number)[1]'
)

When I try to use the CTE in my view to avoid the case statement, E.g.:

, xdata.value(CTE_Selector.Selector, 'varchar(50)')

I get the following error: The argument 1 of the XML data type method "query" must be a string literal.

So in a nutshell, the XML value method seems to demand a constant string. Is there anyway around this?

Answer 1

You can not use a column or variable instead of the XQuery expression. You can use columns or variables in the XQuery expression but I don't think that will help you much in this situation.

sql:column()
sql:variable()

You could use only one call to .value() like this.

xdata.value('(
              /Person/PersonGuid/Personnel/@UserName,
              /Component/ComponentGuid/Component/@Number
             )[1]', 'varchar(50)')