ShredXml.sql

DECLARE @XML XML =
'<CustomDataSet>
    <VersionNumber>3</VersionNumber>
    <DownloadDate>2017-05-05</DownloadDate>
    <Persons>
        <Person>
            <PersonId>1</PersonId>
            <Name>John Xiong</Name>
            <Address>111 Fake Rd</Address>
            <City>Lansing</City>
            <State>MI</State>
            <Zip>48906</Zip>
        </Person>
        <Person>
            <PersonId>2</PersonId>
            <Name>Barack Obama</Name>
            <Address>222 Real Rd</Address>
            <City>Lansing</City>
            <State>MI</State>
            <Zip>48911</Zip>
            <RandomField>ABC</RandomField>
        </Person>
    </Persons>
</CustomDataSet>';

SELECT   customDataSet.cols.value('*:VersionNumber[1]', 'VARCHAR(30)') VersionNumber,
         customDataSet.cols.value('*:DownloadDate[1]', 'DATE') DownloadDate
FROM     @XML.nodes('*:CustomDataSet') customDataSet(cols);

/*
VersionNumber                  DownloadDate
------------------------------ ------------
3                              2017-05-05
*/

SELECT   customDataSet.cols.value('*:VersionNumber[1]', 'VARCHAR(30)') VersionNumber,
         customDataSet.cols.value('*:DownloadDate[1]', 'DATE') DownloadDate,
         person.cols.value('*:PersonId[1]', 'INT') PersonId,
         person.cols.value('*:Name[1]', 'VARCHAR(30)') Name,
         person.cols.value('*:Address[1]', 'VARCHAR(30)') [Address],
         person.cols.value('*:City[1]', 'VARCHAR(30)') [City],
         person.cols.value('*:State[1]', 'VARCHAR(30)') [State],
         person.cols.value('*:Zip[1]', 'VARCHAR(30)') [Zip],
         person.cols.value('*:RandomField[1]', 'VARCHAR(30)') RandomField
FROM     @XML.nodes('*:CustomDataSet') customDataSet(cols)
CROSS APPLY customDataSet.cols.nodes('*:Persons') persons(cols)
CROSS APPLY persons.cols.nodes('*:Person') person(cols);

/*
VersionNumber                  DownloadDate PersonId    Name                           Address                        City                           State                          Zip                            RandomField
------------------------------ ------------ ----------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
3                              2017-05-05   1           John Xiong                     111 Fake Rd                    Lansing                        MI                             48906                          NULL
3                              2017-05-05   2           Barack Obama                   222 Real Rd                    Lansing                        MI                             48911                          ABC
*/