Passing count to SQL query of XML content, NULL response but hard coding fetches the value

Question

I am querying XML content in a table on SQL Server 2014 Service Pack 2 GDR. I can count the desired nodes, and select a specific node but I must dynamically collect the desired node values. Please redirect me to the right syntax and/or method for this.

Instead of the eight values I was expecting from the count, I only get that @loops value printed.

Below is the basic code:

DECLARE @x  XML

SET @x = 

     '<ArrayOfOrderedRequest xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/NewWorld.Aegis.Cad.Common.Broker.Contract.ResponsePlans">
  <OrderedRequest>
    <Request i:type="RequestWithResource">
      <BackupList>
        <RequestBackup>
          <Request i:type="RequestWithResource">
            <NodeID>f1e00f33-0451-4b25-b69a-6c5f6e358fa6</NodeID>
            <Quantity>1</Quantity>
            <Action>Dispatch</Action>
            <Resource>
              <ID>160</ID>
              <ResourceType>1</ResourceType>
            </Resource>
          </Request>
          <Sequence>0</Sequence>
        </RequestBackup>
      </BackupList>
      <NodeID>7b901b61-5c7b-4429-a306-d42f7b367e14</NodeID>
      <Quantity>1</Quantity>
      <Action>Dispatch</Action>
      <Resource>
        <ID>3989</ID>
        <ResourceType>2</ResourceType>
      </Resource>
    </Request>
    <Sequence>1</Sequence>
  </OrderedRequest>
  <OrderedRequest>
    <Request i:type="RequestWithResource">
      <BackupList>
        <RequestBackup>
          <Request i:type="RequestWithResource">
            <NodeID>e4eedaf1-3a7e-4bb5-824c-84efebcab2dd</NodeID>
            <Quantity>1</Quantity>
            <Action>Dispatch</Action>
            <Resource>
              <ID>164</ID>
              <ResourceType>1</ResourceType>
            </Resource>
          </Request>
          <Sequence>0</Sequence>
        </RequestBackup>
      </BackupList>
      <NodeID>1df4b869-5ec8-4582-91f6-28c174aa99ed</NodeID>
      <Quantity>1</Quantity>
      <Action>Dispatch</Action>
      <Resource>
        <ID>3988</ID>
        <ResourceType>2</ResourceType>
      </Resource>
    </Request>
    <Sequence>2</Sequence>
  </OrderedRequest>
  <OrderedRequest>
    <Request i:type="RequestWithResource">
      <NodeID>f30cd65e-5919-44a5-9977-6e04db9a738b</NodeID>
      <Quantity>1</Quantity>
      <Action>Dispatch</Action>
      <Resource>
        <ID>3991</ID>
        <ResourceType>2</ResourceType>
      </Resource>
    </Request>
    <Sequence>3</Sequence>
  </OrderedRequest>
  <OrderedRequest>
    <Request i:type="RequestWithResource">
      <BackupList>
        <RequestBackup>
          <Request i:type="RequestWithResource">
            <NodeID>1df52ab3-ea2f-45e8-af34-f651625cd1ca</NodeID>
            <Quantity>1</Quantity>
            <Action>Dispatch</Action>
            <Resource>
              <ID>124</ID>
              <ResourceType>1</ResourceType>
            </Resource>
          </Request>
          <Sequence>0</Sequence>
        </RequestBackup>
      </BackupList>
      <NodeID>4d1224ee-998d-48e9-8c9c-a74762d0c944</NodeID>
      <Quantity>1</Quantity>
      <Action>Dispatch</Action>
      <Resource>
        <ID>3994</ID>
        <ResourceType>2</ResourceType>
      </Resource>
    </Request>
    <Sequence>4</Sequence>
  </OrderedRequest>
  <OrderedRequest>
    <Request i:type="RequestWithResource">
      <BackupList>
        <RequestBackup>
          <Request i:type="RequestWithResource">
            <NodeID>fee1da03-f490-423d-bdcc-aa7195d560a0</NodeID>
            <Quantity>1</Quantity>
            <Action>Dispatch</Action>
            <Resource>
              <ID>160</ID>
              <ResourceType>1</ResourceType>
            </Resource>
          </Request>
          <Sequence>0</Sequence>
        </RequestBackup>
      </BackupList>
      <NodeID>6c4209fe-9d8c-48fd-8f9f-6a38299ac368</NodeID>
      <Quantity>1</Quantity>
      <Action>Dispatch</Action>
      <Resource>
        <ID>4846</ID>
        <ResourceType>2</ResourceType>
      </Resource>
    </Request>
    <Sequence>5</Sequence>
  </OrderedRequest>
  <OrderedRequest>
    <Request i:type="RequestWithResource">
      <BackupList>
        <RequestBackup>
          <Request i:type="RequestWithResource">
            <NodeID>41ccb2b1-0e6c-4a54-b1bd-ab22152cdd6b</NodeID>
            <Quantity>1</Quantity>
            <Action>Dispatch</Action>
            <Resource>
              <ID>164</ID>
              <ResourceType>1</ResourceType>
            </Resource>
          </Request>
          <Sequence>0</Sequence>
        </RequestBackup>
      </BackupList>
      <NodeID>218da0c7-fdd1-4e20-a62b-feba25a28095</NodeID>
      <Quantity>1</Quantity>
      <Action>Dispatch</Action>
      <Resource>
        <ID>4844</ID>
        <ResourceType>2</ResourceType>
      </Resource>
    </Request>
    <Sequence>6</Sequence>
  </OrderedRequest>
  <OrderedRequest>
    <Request i:type="RequestWithResource">
      <BackupList>
        <RequestBackup>
          <Request i:type="RequestWithResource">
            <NodeID>f86419eb-177f-4e3f-bdc2-6d8fd1e3f123</NodeID>
            <Quantity>1</Quantity>
            <Action>Dispatch</Action>
            <Resource>
              <ID>160</ID>
              <ResourceType>1</ResourceType>
            </Resource>
          </Request>
          <Sequence>0</Sequence>
        </RequestBackup>
      </BackupList>
      <NodeID>d588e812-1607-49a7-b1af-20c3c998a8a6</NodeID>
      <Quantity>1</Quantity>
      <Action>Dispatch</Action>
      <Resource>
        <ID>4538</ID>
        <ResourceType>2</ResourceType>
      </Resource>
    </Request>
    <Sequence>7</Sequence>
  </OrderedRequest>
  <OrderedRequest>
    <Request i:type="RequestWithResource">
      <BackupList>
        <RequestBackup>
          <Request i:type="RequestWithResource">
            <NodeID>dd71e9bb-4d92-47a1-b325-e2e0ed6b7405</NodeID>
            <Quantity>1</Quantity>
            <Action>Dispatch</Action>
            <Resource>
              <ID>164</ID>
              <ResourceType>1</ResourceType>
            </Resource>
          </Request>
          <Sequence>0</Sequence>
        </RequestBackup>
      </BackupList>
      <NodeID>3e125708-28ba-4629-b096-d565030cb5c2</NodeID>
      <Quantity>1</Quantity>
      <Action>Dispatch</Action>
      <Resource>
        <ID>4277</ID>
        <ResourceType>2</ResourceType>
      </Resource>
    </Request>
    <Sequence>8</Sequence>
  </OrderedRequest>
</ArrayOfOrderedRequest>'


 DECLARE @loops INT

-- Total count of <ID> Nodes -- in this case calling the variable loops
SELECT RPL.ResourceXML.value('declare namespace ns="http://schemas.datacontract.org/2004/07/NewWorld.Aegis.Cad.Common.Broker.Contract.ResponsePlans";
 count(/ns:ArrayOfOrderedRequest/ns:OrderedRequest/ns:Request/ns:Resource/ns:ID)', 'int')  as loops
from   
NwsAegisCAD.Response.ResponsePlan as RPL 
where  RPL.ResponsePlanID =226;

DECLARE @i INT
-- Set counter variable to 1
SET @i = 1 

-- loop starts
WHILE  @i <=  @loops  
BEGIN

 -- variable to store UNIT ID
DECLARE @UnitID  INT

--select unitid to the variable

Select
   @UnitID= @x.value('ID[1]', 'INT')   
FROM @x.nodes('/ArrayOfOrderedRequest/OrderedRequest/Request/Resource/ID[position()=sql:variable("@i")]')
e(x)

-- increment counter    
SET @i = @i + 1   

PRINT @UnitID

END

---

If I want a specific node, or the values encased in their xml tags, then this query works I have been wildly unsuccessful in getting the @i parameter passed to the xml.value query

Select RPL.Name as PlanName,  

RPL.ResourceXML.value('declare namespace ns="http://schemas.datacontract.org/2004/07/NewWorld.Aegis.Cad.Common.Broker.Contract.ResponsePlans";
 count(/ns:ArrayOfOrderedRequest/ns:OrderedRequest/ns:Request/ns:Resource/ns:ID)', 'int') as COUNT_unitIDs,

RPL.ResourceXML.query('declare namespace ns=
"http://schemas.datacontract.org/2004/07/NewWorld.Aegis.Cad.Common.Broker.Contract.ResponsePlans"; 
for $unitID in 
/ns:ArrayOfOrderedRequest/ns:OrderedRequest/ns:Request/ns:Resource/ns:ID  order by $unitID descending return $unitID') as all_the_RPlan_unitIDs ,

RPL.ResourceXML.value('declare namespace ns="http://schemas.datacontract.org/2004/07/NewWorld.Aegis.Cad.Common.Broker.Contract.ResponsePlans";
(/ns:ArrayOfOrderedRequest/ns:OrderedRequest/ns:Request/ns:Resource/ns:ID)[1]', 'int') as first_UnitID

from   
NwsAegisCAD.Response.ResponsePlan as RPL 
where  RPL.ResponsePlanID =226;

............................................. ..............................................

I have read and am re-reading these previously posted inquiries, and both MS articles.

How do I iterate through the Nodes of a XML Field in T-SQL?

sql server xml.value skeleton dynamic cases

Pass the Count(*) to variable via T-SQL

XQUERY - How to use the sql:variable in 'value()' function?

https://learn.microsoft.com/en-us/sql/t-sql/xml/value-method-xml-data-type?view=sql-server-2017

https://learn.microsoft.com/en-us/sql/xquery/xquery-extension-functions-sql-variable?view=sql-server-2017

Thank you for your time and any hints you might share on how to solve this.

Answer 1

For your next question: Please reduce sample data to den needed minimum. If there ist more than 1, then 2 is enough...

You did not state the expected output, but the following will return each and everything. I had to make some asumptions about 1:n and 1:1 related data. So this might need some modifications.

No need for a loop for sure (avoid loops if ever possible!).

If you want to pick a special <Request> you can place a predicate within .nodes():

DECLARE @x XML

SET @x =
N'<ArrayOfOrderedRequest xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/NewWorld.Aegis.Cad.Common.Broker.Contract.ResponsePlans">
    <OrderedRequest>
    <Request i:type="RequestWithResource">
        <BackupList>
        <RequestBackup>
            <Request i:type="RequestWithResource">
            <NodeID>f1e00f33-0451-4b25-b69a-6c5f6e358fa6</NodeID>
            <Quantity>1</Quantity>
            <Action>Dispatch</Action>
            <Resource>
                <ID>160</ID>
                <ResourceType>1</ResourceType>
            </Resource>
            </Request>
            <Sequence>0</Sequence>
        </RequestBackup>
        </BackupList>
        <NodeID>7b901b61-5c7b-4429-a306-d42f7b367e14</NodeID>
        <Quantity>1</Quantity>
        <Action>Dispatch</Action>
        <Resource>
        <ID>3989</ID>
        <ResourceType>2</ResourceType>
        </Resource>
    </Request>
    <Sequence>1</Sequence>
    </OrderedRequest>
    <OrderedRequest>
    <Request i:type="RequestWithResource">
        <BackupList>
        <RequestBackup>
            <Request i:type="RequestWithResource">
            <NodeID>e4eedaf1-3a7e-4bb5-824c-84efebcab2dd</NodeID>
            <Quantity>1</Quantity>
            <Action>Dispatch</Action>
            <Resource>
                <ID>164</ID>
                <ResourceType>1</ResourceType>
            </Resource>
            </Request>
            <Sequence>0</Sequence>
        </RequestBackup>
        </BackupList>
        <NodeID>1df4b869-5ec8-4582-91f6-28c174aa99ed</NodeID>
        <Quantity>1</Quantity>
        <Action>Dispatch</Action>
        <Resource>
        <ID>3988</ID>
        <ResourceType>2</ResourceType>
        </Resource>
    </Request>
    <Sequence>2</Sequence>
    </OrderedRequest>
    <OrderedRequest>
    <Request i:type="RequestWithResource">
        <NodeID>f30cd65e-5919-44a5-9977-6e04db9a738b</NodeID>
        <Quantity>1</Quantity>
        <Action>Dispatch</Action>
        <Resource>
        <ID>3991</ID>
        <ResourceType>2</ResourceType>
        </Resource>
    </Request>
    <Sequence>3</Sequence>
    </OrderedRequest>
    <OrderedRequest>
    <Request i:type="RequestWithResource">
        <BackupList>
        <RequestBackup>
            <Request i:type="RequestWithResource">
            <NodeID>1df52ab3-ea2f-45e8-af34-f651625cd1ca</NodeID>
            <Quantity>1</Quantity>
            <Action>Dispatch</Action>
            <Resource>
                <ID>124</ID>
                <ResourceType>1</ResourceType>
            </Resource>
            </Request>
            <Sequence>0</Sequence>
        </RequestBackup>
        </BackupList>
        <NodeID>4d1224ee-998d-48e9-8c9c-a74762d0c944</NodeID>
        <Quantity>1</Quantity>
        <Action>Dispatch</Action>
        <Resource>
        <ID>3994</ID>
        <ResourceType>2</ResourceType>
        </Resource>
    </Request>
    <Sequence>4</Sequence>
    </OrderedRequest>
    <OrderedRequest>
    <Request i:type="RequestWithResource">
        <BackupList>
        <RequestBackup>
            <Request i:type="RequestWithResource">
            <NodeID>fee1da03-f490-423d-bdcc-aa7195d560a0</NodeID>
            <Quantity>1</Quantity>
            <Action>Dispatch</Action>
            <Resource>
                <ID>160</ID>
                <ResourceType>1</ResourceType>
            </Resource>
            </Request>
            <Sequence>0</Sequence>
        </RequestBackup>
        </BackupList>
        <NodeID>6c4209fe-9d8c-48fd-8f9f-6a38299ac368</NodeID>
        <Quantity>1</Quantity>
        <Action>Dispatch</Action>
        <Resource>
        <ID>4846</ID>
        <ResourceType>2</ResourceType>
        </Resource>
    </Request>
    <Sequence>5</Sequence>
    </OrderedRequest>
    <OrderedRequest>
    <Request i:type="RequestWithResource">
        <BackupList>
        <RequestBackup>
            <Request i:type="RequestWithResource">
            <NodeID>41ccb2b1-0e6c-4a54-b1bd-ab22152cdd6b</NodeID>
            <Quantity>1</Quantity>
            <Action>Dispatch</Action>
            <Resource>
                <ID>164</ID>
                <ResourceType>1</ResourceType>
            </Resource>
            </Request>
            <Sequence>0</Sequence>
        </RequestBackup>
        </BackupList>
        <NodeID>218da0c7-fdd1-4e20-a62b-feba25a28095</NodeID>
        <Quantity>1</Quantity>
        <Action>Dispatch</Action>
        <Resource>
        <ID>4844</ID>
        <ResourceType>2</ResourceType>
        </Resource>
    </Request>
    <Sequence>6</Sequence>
    </OrderedRequest>
    <OrderedRequest>
    <Request i:type="RequestWithResource">
        <BackupList>
        <RequestBackup>
            <Request i:type="RequestWithResource">
            <NodeID>f86419eb-177f-4e3f-bdc2-6d8fd1e3f123</NodeID>
            <Quantity>1</Quantity>
            <Action>Dispatch</Action>
            <Resource>
                <ID>160</ID>
                <ResourceType>1</ResourceType>
            </Resource>
            </Request>
            <Sequence>0</Sequence>
        </RequestBackup>
        </BackupList>
        <NodeID>d588e812-1607-49a7-b1af-20c3c998a8a6</NodeID>
        <Quantity>1</Quantity>
        <Action>Dispatch</Action>
        <Resource>
        <ID>4538</ID>
        <ResourceType>2</ResourceType>
        </Resource>
    </Request>
    <Sequence>7</Sequence>
    </OrderedRequest>
    <OrderedRequest>
    <Request i:type="RequestWithResource">
        <BackupList>
        <RequestBackup>
            <Request i:type="RequestWithResource">
            <NodeID>dd71e9bb-4d92-47a1-b325-e2e0ed6b7405</NodeID>
            <Quantity>1</Quantity>
            <Action>Dispatch</Action>
            <Resource>
                <ID>164</ID>
                <ResourceType>1</ResourceType>
            </Resource>
            </Request>
            <Sequence>0</Sequence>
        </RequestBackup>
        </BackupList>
        <NodeID>3e125708-28ba-4629-b096-d565030cb5c2</NodeID>
        <Quantity>1</Quantity>
        <Action>Dispatch</Action>
        <Resource>
        <ID>4277</ID>
        <ResourceType>2</ResourceType>
        </Resource>
    </Request>
    <Sequence>8</Sequence>
    </OrderedRequest>
</ArrayOfOrderedRequest>';

--The query declares the namespaces ahead...

WITH XMLNAMESPACES(DEFAULT 'http://schemas.datacontract.org/2004/07/NewWorld.Aegis.Cad.Common.Broker.Contract.ResponsePlans'
                            ,'http://www.w3.org/2001/XMLSchema-instance' AS i)
SELECT oRq.value(N'(Request/@i:type)[1]',N'nvarchar(max)') AS Request_Type
        ,oRq.value(N'(Request/NodeID/text())[1]',N'uniqueidentifier') AS Request_ID
        ,oRq.value(N'(Request/Quantity/text())[1]',N'int') AS Request_Quantity
        ,oRq.value(N'(Request/Action/text())[1]',N'nvarchar(max)') AS Request_Action

        --Seems to be 1:1, therefore no .nodes() but just a longer XPath
        ,oRq.value(N'(Request/Resource/ID/text())[1]',N'int') AS Request_Resource_ID
        ,oRq.value(N'(Request/Resource/ResourceType/text())[1]',N'int') AS Request_Resource_ResourceType

        --Sequence is living on the same level as <Request>
        ,oRq.value(N'(Sequence/text())[1]',N'int') AS Request_Sequence

        --Seems to be 1:1 too, the usage of .nodes() is for better readability, a longer XPath was okay as well
        ,rBkp.value(N'(Request/@i:type)[1]',N'nvarchar(max)') AS Backup_Request_Type
        ,rBkp.value(N'(Request/NodeID/text())[1]',N'uniqueidentifier') AS Backup_Request_ID
        ,rBkp.value(N'(Request/Quantity/text())[1]',N'int') AS Backup_Request_Quantity
        ,rBkp.value(N'(Request/Action/text())[1]',N'nvarchar(max)') AS Backup_Request_Action
FROM @x.nodes(N'/ArrayOfOrderedRequest/OrderedRequest') AS A(oRq)       --All OrderRequests
OUTER APPLY oRq.nodes(N'Request/BackupList/RequestBackup') AS B(rBkp);  --a BackupList (if it exists)