Search code examples
sqlxmlt-sqlssms

SQL XML Node Extraction

I am relatively new to work with XML data in SQL so please forgive any "silly" mistakes :p.

In essence what I am trying to do is, extract the "Before" & "After" values from the following XML:

<AuditData>
  <Field Name="BrandEmailSubscribed">
    <Before Value="True" />
    <After Value="False" />
  </Field>
  <Metadata Type="OperatorInstigated">
    <Note></Note>
  </Metadata>
</AuditData>

I have tried using the following SQL however this is returning a blank value (not NULL though).

select top 10 C.B.value('(Before)[1]','VARCHAR(100)'),AD.* from Table AD
OUTER APPLY AD.Data.nodes('AuditData/Field') AS C(B)

Any help is much appreciated :).

Solution

  • You need to specify the attribute to be extracted

    select top 10 C.B.value('(Before/@Value)[1]','VARCHAR(100)'), AD.* 
from Table AD
OUTER APPLY AD.Data.nodes('AuditData/Field') AS C(B)