Query XML node names

• MacDaddy

  SSC Eights!

  Points: 993

  More actions

  April 19, 2016 at 4:42 am

  #325737

  I hate XML which is probably why I can't do this, which just makes me hate XML even more!

  That personal grievance aside I need help extracting XML nodes from a database column. I don't need the values of the data contained in the nodes, I think I've done that with a bit of help from Google, but I need the actual names of the nodes in which the data is contained, if that makes any sense.

  Since most things that Google is turning up is how to extract the data values it's not helped much in what I need to do.

  If I have XML data that looks something like this:

  <TaxAudit xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <Year>20152016</Year>

  <AccountNumber>1234567890</AccountNumber>

  <CertificateRequested>true</CertificateRequested>

  </TaxAudit>

  How do I get a query to spit out Year, AccountNumber and CertificateRequested in the output?

  Apologies for my ineptitude in this regard and many thanks in advance,

  Christophe

  Keep the rubber side down and the shiny side up.

• Ian Scarlett

  SSC-Insane

  Points: 23197

  More actions

  April 19, 2016 at 4:54 am

  #1872993

  I'm with you with regard hating XML, but does this do what you want:-

  DECLARE @input XML = '<TaxAudit xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <Year>20152016</Year>

  <AccountNumber>1234567890</AccountNumber>

  <CertificateRequested>true</CertificateRequested>

  </TaxAudit>'

  SELECT

  NodeName = C.value('local-name(.)', 'varchar(50)')

  FROM @input.nodes('/TaxAudit/*') AS T(C)

• MacDaddy

  SSC Eights!

  Points: 993

  More actions

  April 19, 2016 at 7:46 am

  #1873069

  Ian,

  That does just the job, thank you so much!

  :w00t:

  Christophe

  Keep the rubber side down and the shiny side up.