XQuery [value()] issue for Dynamic SQL variable in Select Stateemt

Question

I'm try to make the code below dynamic as there can be different names for the 'name' attribute in the page 'node'.

When I run the code below it returns the error 'XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *''

I feel my main issue is in the line 'C.value('(/level1/level2/template/page/@name)[sql:variable("@Counter")]','NVARCHAR(MAX)') AS [Page Name]' where I am trying to make '@counter' variable dynamic.

Does anyone have any solutions please?

XML FOR ID 9371

<level1>
  <level2>
    <template width="594" height="500">
      <page Cid="1" name="Test Page Name" colour="-3355393">
        <image Cid="8" x="432" y="8" w="148" h="95" KeyImage="32861" Ratio="y" />
        <formattedText Cid="14" x="9" y="22" w="253" h="38">
          <p>
            <p>
              Text
            </p>
          </p>
        </formattedText>
      </page>
      <page Cid="6" name="Properties">
        <formattedText Cid="7" x="200" y="148" w="208" h="228">
          <p>
            <p>
              <t>Created by </t>
              <t b="b">Joe Bloggs</t>
            </p>
            <p />
            <p>
              <t>Date published 30/05/2017</t>
            </p>
        </formattedText>
      </page>
    </template>
  </level2>
</level1>

RESULTS FROM SQL Query

** Present Result

Page Name   | Demographics  
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Test Page Name  | <page Cid="1" name="Test Page Name" colour="-3355393"><image Cid="8" x="432" y="8" w="148" h="95" KeyImage="32861" Ratio="y" /><formattedText Cid="14" x="9" y="22" w="253" h="38"><p><p>Text</p></p> </formattedText></page>
Test Page Name  | <page><formattedText Cid="7" x="200" y="148" w="208" h="228"><p><p> <t>Created by </t><t b="b">Joe Bloggs</t></p><p /><p><t>Date published 30/05/2017</t> </p></formattedText></page>

** Desired Result

Page Name   | Demographics  
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Test Page Name  | <page Cid="1" name="Test Page Name" colour="-3355393"><image Cid="8" x="432" y="8" w="148" h="95" KeyImage="32861" Ratio="y" /><formattedText Cid="14" x="9" y="22" w="253" h="38"><p><p>Text</p></p> </formattedText></page>
Properties  | <page><formattedText Cid="7" x="200" y="148" w="208" h="228"><p><p> <t>Created by </t><t b="b">Joe Bloggs</t></p><p /><p><t>Date published 30/05/2017</t> </p></formattedText></page> 



DECLARE @TableName VARCHAR(40),
 @opxml AS XML,
 @hDoc AS INT,
 @SQL NVARCHAR (MAX)
   


SELECT @opxml = 
a.[filedata]
FROM [database].[dbo].[xmlfile2] a
where  [Id] = 9371

DECLARE @Counter INT 
DECLARE @MaxNo INT 
SET @Counter=1
SET @MaxNo=(SELECT
COUNT(CAST('<page>' +  CAST(C.query('./child::node()') as nvarchar(max)) + '</page>' as xml))
FROM @opxml.nodes('/level1/level2/template/page') AS T(C))

WHILE ( @Counter <= @MaxNo)
BEGIN

SELECT
C.value('(/level1/level2/template/page/@name)[sql:variable("@Counter")]','NVARCHAR(MAX)') AS         
[Page Name],
CAST('<page>' +  CAST(C.query('./child::node()') as nvarchar(max)) + '</page>' as xml) AS     [Page_XML]
, ROW_NUMBER() OVER (ORDER BY t.c)
FROM @opxml.nodes('/level1/level2/template/page') AS T(C)


SET @Counter  = @Counter  + 1
END

While asking a question, you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Jul 30, 2021 at 14:50
Does using (/level1/level2/template/page/@name)[position() = sql:variable("@Counter")] instead of (/level1/level2/template/page/@name)[sql:variable("@Counter")] improve things? — Martin Honnen
– Martin Honnen, Commented Jul 30, 2021 at 16:43
Note that the XML sample is not well-formed, a closing </p> tag is missing. As for the whole task, do you want to extract the existing page elements? I don't quite understand why the first result row has the original page with its attributes while the second result row has a simple page without the attributes from the input. — Martin Honnen
– Martin Honnen, Commented Aug 2, 2021 at 10:25

Martin Honnen · Accepted Answer · 2021-08-02 10:22:34Z

I think

SELECT
  C.value('@name','NVARCHAR(MAX)') AS [Page Name],
  C.query('<page>{node()}</page>') AS [Page_XML], 
  ROW_NUMBER() OVER (ORDER BY t.c)
FROM @opxml.nodes('/level1/level2/template/page') AS T(C)

might suffice to produce the wanted result without the need for a WHILE loop.

I think the original problem with the long path inside of value() (e.g. C.value('(/level1/level2/template/page/@name)[sql:variable("@Counter")]','NVARCHAR(MAX)')) is due to the static type checking of XQuery in SQL server and to avoid it you basically need to add another predicate that ensures the type checker knows a single value is returned e.g. C.value('(/level1/level2/template/page/@name)[sql:variable("@Counter")][1]','NVARCHAR(MAX)')

For me, the code

DECLARE @opxml AS XML

SET @opxml = N'<level1>
  <level2>
    <template width="594" height="500">
      <page Cid="1" name="Test Page Name" colour="-3355393">
        <image Cid="8" x="432" y="8" w="148" h="95" KeyImage="32861" Ratio="y" />
        <formattedText Cid="14" x="9" y="22" w="253" h="38">
          <p>
            <p>
              Text
            </p>
          </p>
        </formattedText>
      </page>
      <page Cid="6" name="Properties">
        <formattedText Cid="7" x="200" y="148" w="208" h="228">
          <p>
            <p>
              <t>Created by </t>
              <t b="b">Joe Bloggs</t>
            </p>
            <p />
            <p>
              <t>Date published 30/05/2017</t>
            </p>
          </p>
        </formattedText>
      </page>
    </template>
  </level2>
</level1>'

SELECT
  C.value('@name','NVARCHAR(MAX)') AS [Page Name],
  C.query('<page>{node()}</page>') AS [Page_XML], 
  ROW_NUMBER() OVER (ORDER BY t.c)
FROM @opxml.nodes('/level1/level2/template/page') AS T(C)

produces the table

Page Name   Page_XML    (Kein Spaltenname)
Test Page Name  <page><image Cid="8" x="432" y="8" w="148" h="95" KeyImage="32861" Ratio="y" /><formattedText Cid="14" x="9" y="22" w="253" h="38"><p><p>
              Text
            </p></p></formattedText></page> 1
Properties  <page><formattedText Cid="7" x="200" y="148" w="208" h="228"><p><p><t>Created by </t><t b="b">Joe Bloggs</t></p><p /><p><t>Date published 30/05/2017</t></p></p></formattedText></page> 2

so at least the Page_Name seems to be easily populated by using e.g. C.value('@name','NVARCHAR(MAX)') AS [Page Name]

Have now given more to what I am trying to achieve in my original question.

Collectives™ on Stack Overflow

XQuery [value()] issue for Dynamic SQL variable in Select Stateemt

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related