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

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

Answer

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]