TSQL: How to select all field when used xquery

T.Zacks 3,986 Reputation points
2021-08-16T12:19:36.243+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
0 comments No comments
{count} votes

Accepted answer
  1. Olaf Helper 41,411 Reputation points
    2021-08-16T12:50:36.537+00:00

    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)
    
    0 comments No comments

0 additional answers

Sort by: Most helpful