StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The problem with what you are trying to do is that although you may know that your select query will only return one result, the database does not. Consider this sample schema</p> <pre><code>CREATE TABLE Base (BaseXML XML, Settings INT); INSERT Base VALUES ('<root settings="1"><a>SomeXML</a></root>', NULL); </code></pre> <p>You know that settings only appears once in the root, so this </p> <pre><code>SELECT a.a.value('@settings', 'VARCHAR(50)') AS [Settings] FROM Base X CROSS APPLY X.BaseXML.nodes('root') a(a); </code></pre> <p>Will only return one result (for each row), however, if you take this schema:</p> <pre><code>CREATE TABLE Base2 (BaseXML XML, Settings INT); INSERT Base2 VALUES ('<root><a settings="1">SomeXML</a><a settings="2">Some More XML</a></root>', NULL); </code></pre> <p>An almost identical query will return 2 rows:</p> <pre><code>SELECT a.a.value('@settings', 'VARCHAR(50)') AS [Settings] FROM Base2 X CROSS APPLY X.BaseXML.nodes('root/a') a(a); </code></pre> <p>Therefore your function cannot work as a scalar function. You need to tell SQL that you are only expecting one (the first) value back by using [1]:</p> <pre><code>SELECT BaseXML.value('/root[1]/@settings[1]', 'INT') AS Settings FROM Base; </code></pre> <p><strong><a href="http://www.sqlfiddle.com/#!3/c19d1/8" rel="nofollow">SQL FIddle to demonstrate multiple return values</a></strong></p> <p>I would suggest the best way to do this though is rather than send the LineID as a parameter, to send the XML itself. Since you want a computed column you can just send the XML data and save a needless second query of the table:</p> <pre><code>CREATE FUNCTION dbo.ParseBaseSettings (@XML XML) RETURNS INT AS BEGIN RETURN @XML.value('/root[1]/@settings[1]', 'INT'); END; </code></pre> <p>You will probably need to handle your XML Namespaces in here, but for example's sake I have skipped this.</p> <p>Finally you can create your computed column:</p> <pre><code>CREATE TABLE Base ( BaseXML XML, Settings AS dbo.ParseBaseSettings(BaseXML) ); </code></pre> <p><strong><a href="http://www.sqlfiddle.com/#!3/769b8/1" rel="nofollow">SQL Fiddle of final solution</a></strong></p> <p><strong>EDIT</strong></p> <p>With your actual XML data I have compiled this function:</p> <pre><code>CREATE FUNCTION dbo.ParseBaseSettings (@XML XML) RETURNS NVARCHAR(50) AS BEGIN DECLARE @Ret NVARCHAR(50); WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema' AS als) SELECT @Ret = @XML.value('/als:Doc[1]/@Settings', 'NVARCHAR(50)'); RETURN @Ret; END; </code></pre>

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