StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POParameter to xml value method through a join result
text
Body
copied!<p>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.</p> <p>E.g.</p> <pre><code>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 </code></pre> <p>This case statement is quite large and unwieldy.</p> <p>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):</p> <pre><code>;WITH CTE_Selector (TableName, Selector) AS ( SELECT 'Person', '(/*/PersonGuid/Personnel/@UserName)[1]' UNION SELECT 'Component' ,'(/*/ComponentGuid/Component/@Number)[1]' ) </code></pre> <p>When I try to use the CTE in my view to avoid the case statement, E.g.:</p> <pre><code>, xdata.value(CTE_Selector.Selector, 'varchar(50)') </code></pre> <p>I get the following error: <strong>The argument 1 of the XML data type method "query" must be a string literal.</strong></p> <p>So in a nutshell, the XML value method seems to demand a constant string. Is there anyway around this?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload