TSQL: How to select all field when used xquery

Question

This is my sample query.

Declare @Xml xml=N'<NewDataSet>
  <Table1>
    <ID>0</ID>
    <Ticker>GRPN</Ticker>
    <ClientCode>GRPN</ClientCode>
    <GroupName>Group1</GroupName>
    <FieldName>Last Update</FieldName>
    <FieldValue>5/7/2021</FieldValue>
  </Table1>
  <Table1>
    <ID>0</ID>
    <Ticker>GRPN</Ticker>
    <ClientCode>GRPN</ClientCode>
    <GroupName>Group1</GroupName>
    <FieldName>Broker</FieldName>
    <FieldValue>Barclays Capital</FieldValue>
  </Table1>
</NewDataSet>'

    SELECT v.*               
    FROM @Xml.nodes('/NewDataSet/Table1') AS d(v)

I am not good in sql server. so please guide me how to select all fields when using xquery. for normal sql we use like select * from table1. how to do the same when use xquery?

Thanks

Answer 1

use like select * from table1. how to do the same when use xquery?

You can not.
If you don't know the element names, then you can use the numeric element ordinate, but if someone changes element order (which is not important for valid XML), then you get unexpected results.

Declare @Xml xml=N'<NewDataSet>
   <Table1>
     <ID>0</ID>
     <Ticker>GRPN</Ticker>
     <ClientCode>GRPN</ClientCode>
     <GroupName>Group1</GroupName>
     <FieldName>Last Update</FieldName>
     <FieldValue>5/7/2021</FieldValue>
   </Table1>
   <Table1>
     <ID>0</ID>
     <Ticker>GRPN</Ticker>
     <ClientCode>GRPN</ClientCode>
     <GroupName>Group1</GroupName>
     <FieldName>Broker</FieldName>
     <FieldValue>Barclays Capital</FieldValue>
   </Table1>
 </NewDataSet>'

SELECT d.v.value('(.//*)[1]', 'varchar(100)') AS val1,
       d.v.value('(.//*)[2]', 'varchar(100)') AS val2,
       d.v.value('(.//*)[3]', 'varchar(100)') AS val3,
       d.v.value('(.//*)[4]', 'varchar(100)') AS val4,
       d.v.value('(.//*)[5]', 'varchar(100)') AS val5,
       d.v.value('(.//*)[6]', 'varchar(100)') AS val6,
       d.v.value('(.//*)[7]', 'varchar(100)') AS val7 -- Don't exists, returns NULL
FROM @Xml.nodes('/NewDataSet/Table1') AS d(v)