Xquery - Retrieving fields from an XML field

• abrahamb

  SSC Veteran

  Points: 248

  More actions

  January 2, 2008 at 11:24 am

  #177822

  Is there any efficient way to list all the fields in an XML document in Xquery without having to specify each field in a query? I have a doc that looks like:

  ....

  xxxx </FIELDn

  I can get FIELD1 by doing:

  SELECT XML_DATA.query('data(/ROOT/ROWS/FIELD1)') AS FIELD1

  FROM Tablename

  But I need all the fields at once and there are 300 so I don't want to specify each field at a time.

  Please HELP!

  Thanks!

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  January 2, 2008 at 1:00 pm

  #764174

  SELECT r.value('local-name(.)','varchar(10)') AS name,

  r.value('data(.)','varchar(50)') AS value

  FROM Tablename

  CROSS APPLY XML_DATA.nodes('/ROOT/ROWS/*') AS x(r)

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• abrahamb

  SSC Veteran

  Points: 248

  More actions

  January 2, 2008 at 1:09 pm

  #764179

  Excellent - you rock!!! That was exactly what I needed. I can now spend the next several days trying to figure out how it works....:)

• abrahamb

  SSC Veteran

  Points: 248

  More actions

  January 3, 2008 at 8:44 am

  #764399

  Does anyone know how to bring back the data in relational table format? (new report requirment)

  Currently (with the above solution) I get:

  name1 value1

  ...

  namen valuen

  How can I get it back as column names with values?

  Name1 Name2 Name3...Namen

  value1 valuse2 Value3...Valuen