SQL: Query a List within a List XML

Question

I'm trying to query some XML in SQL Server but am having difficulties:

Here is some sample XML:

<BaseReport>
  <Parties>
    <Party>
     <SubjectType>
      <ListItem Name="SubjectType1Name" />
      <ListItem Name="SubjectType2Name" />
     </SubjectType>
    </Party>
    <Party>
      <SubjectType>
       <ListItem Name="SubjectType1Name" />
       <ListItem Name="SubjectType2Name" />
     </SubjectType>
    </Party>
  </Parties>
</BaseReport>

My goal is to extract the Subject Type name from my xml. I'm not sure how to query the list within a list though. I want to grab the first Party's first subject type. I've tried the following to no avail:

 SELECT myXML.value('(/BaseReport/Parties/Party/SubjectType/@Name)[1]', 'varchar(50)') as Name_Type
 FROM MyTable

And it returns all nulls (aka my query is wrong). Any suggestions?

Answer 1

You wrote:

I want to grab the first Party's first subject type

So, the correct XPath should be:

/BaseReport/Parties/Party[1]/SubjectType/ListItem[1]/@Name