How to retrieve all values from xml in SQL

Question

I am pretty new in SQL and stuck in a very common scenario. I have a xml with nested child as input parameter in my Stored Procedure

    <XML>
    <Id>1</Id>
    <Age>1</Age>
    <Address>Test</Address>
    .
    .
    <Days>
      <long>1</long>
      <long>2</long>
      <long>3</long>
      <long>7</long>
    </Days>
  </XML>

I am able to pick data inside XML tag through

Id = t.Xml.value('(./Id)[last()]','int')

but I have no idea how to pick all long values inside XML/Days. I tried

 long = t.UserXml.value('.','int')

and it returns all the values as 1237 but this doesn't solve my problem. I needed values in different rows and long values need to be inserted into another table.

Please let me know the possible solutions for the same. Thanks

Answer 1

Solution for Days on Sqlserver could be

DECLARE @XML XML
SET @XML='<XML>
    <Id>1</Id>
    <Age>1</Age>
    <Address>Test</Address>
    <Days>
      <long>1</long>
      <long>2</long>
      <long>3</long>
      <long>7</long>
    </Days>
  </XML>'

SELECT 
    x.y.value('text()[1]', 'int') as xx
FROM @xml.nodes('XML/Days/long') x(y)