How to query for Xml values and attributes from table in SQL Server

Question

I have a table that contains a Xml column   SELECT    FROM Sqm     A sample of the xml data of a row would be    lt Sqm version  1 2  gt     lt Metrics gt       lt Metric id  TransactionCleanupThread RecordUsedTransactionShift  type  timer  unit    s  count  1  sum  21490    average  21490    minValue  73701     maxValue  73701                                 gt 73701 lt  Metric gt       lt Metric id  TransactionCleanupThread RefundOldTrans              type  timer  unit    s  count  1  sum  184487   average  184487   minValue  632704    maxValue  632704                                gt 632704 lt  Metric gt       lt Metric id  Database CreateConnection SaveContextUserGUID        type  timer  unit    s  count  2  sum  7562     average  3781     minValue  12928     maxValue  13006     standardDeviation  16       gt 12967 lt  Metric gt       lt Metric id  Global CurrentUser                                   type  timer  unit    s  count  6  sum  4022464  average  670411   minValue  15        maxValue  13794345  standardDeviation  1642047  gt 2299194 lt  Metric gt       lt Metric id  Global CurrentUser FetchIdentityFromDatabase         type  timer  unit    s  count  1  sum  4010057  average  4010057  minValue  13752614  maxValue  13752614                              gt 13752614 lt  Metric gt     lt  Metrics gt   lt  Sqm gt    In the case of this data  I would want   SqmId  id                                                   type   unit  count  sum      minValue  maxValue  standardDeviation  Value                                                                                                                                        1      TransactionCleanupThread RecordUsedTransactionShift  timer    s    1      21490    73701     73701     NULL               73701 1      TransactionCleanupThread RefundOldTrans              timer    s    1      184487   632704    632704    NULL               632704 1      Database CreateConnection SaveContextUserGUID        timer    s    2      7562     12928     13006     16                 12967 1      Global CurrentUser                                   timer    s    6      4022464  15        13794345  1642047            2299194 1      Global CurrentUser FetchIdentityFromDatabase         timer    s    1      4010057  13752614  13752614  NULL               13752614 2            In the end I ll actually be performing SUM    MIN    MAX   aggregation  But for now I m just trying to query an xml column   In pseudo-code  I would try something like   SELECT     SqmId      Data query   Sqm Metrics Metric  id   AS id      Data query   Sqm Metrics Metric  type   AS type      Data query   Sqm Metrics Metric  unit   AS unit      Data query   Sqm Metrics Metric  sum   AS sum      Data query   Sqm Metrics Metric  count   AS count      Data query   Sqm Metrics Metric  minValue   AS minValue      Data query   Sqm Metrics Metric  maxValue   AS maxValue      Data query   Sqm Metrics Metric  standardDeviation   AS standardDeviation      Data query   Sqm Metrics Metric   AS value FROM Sqm   But that SQL query doesn t work      Msg 2396  Level 16  State 1  Line 2   XQuery  Sqm data query     Attribute may not appear outside of an element   I ve hunted  and it s amazing how poorly documented  or exampled  Xml querying is  Most resources rather than querying a table  query a variable  which I m not doing  Most resources only use xml querying for filtering and selection  rather than reading values  Most resources read hard-coded child nodes  by index   rather than actual values   Related resources that I read   https   stackoverflow com questions 966441 xml-query-in-sql-server-2008 SQL Server query xml attribute for an element value SQL querying XML attributes SQL Server 2005 XQuery and XML-DML - Part 1 BOL  XML Support in Microsoft SQL Server 2005 Querying XML in SQL Server Basic SQL Server XML Querying BOL  query   Method  xml Data Type  XML Workshop V - Reading Values from XML Columns SQL SERVER     Introduction to Discovering XML Data Type Methods     A Primer   Update   value rather than  query  I tried randomly using  value  in place of  query   SELECT     Sqm SqmId      Data value   Sqm Metrics Metric  id    varchar max    AS id      Data value   Sqm Metrics Metric  type    varchar max    AS type      Data value   Sqm Metrics Metric  unit    varchar max    AS unit      Data value   Sqm Metrics Metric  sum    varchar max    AS sum      Data value   Sqm Metrics Metric  count    varchar max    AS count      Data value   Sqm Metrics Metric  minValue    varchar max    AS minValue      Data value   Sqm Metrics Metric  maxValue    varchar max    AS maxValue      Data value   Sqm Metrics Metric  standardDeviation    varchar max    AS standardDeviation      Data value   Sqm Metrics Metric    varchar max    AS value FROM Sqm   But that also doesn t work       Msg 2389  Level 16  State 1  Line 3 XQuery  Sqm data value        value    requires a singleton  or empty sequence   found operand of   type  xdt untypedAtomic

User · Answer

use value instead of query  must specify index of node to return in the XQuery as well as passing the sql data type to return as the second parameter    select     xt Id       x m value    id 1     varchar max     MetricId from     XmlTest xt     cross apply xt XmlData nodes    Sqm Metrics Metric    x m

User · Answer

I don t understand why some people are suggesting using cross apply or outer apply to convert the xml into a table of values   For me  that just brought back way too much data   Here s my example of how you d create an xml object  then turn it into a table    I ve added spaces in my xml string  just to make it easier to read    DECLARE  str nvarchar 2000   SET  str      SET  str    str     lt users gt   SET  str    str       lt user gt   SET  str    str          lt firstName gt Mike lt  firstName gt   SET  str    str          lt lastName gt Gledhill lt  lastName gt   SET  str    str          lt age gt 31 lt  age gt   SET  str    str       lt  user gt   SET  str    str       lt user gt   SET  str    str          lt firstName gt Mark lt  firstName gt   SET  str    str          lt lastName gt Stevens lt  lastName gt   SET  str    str          lt age gt 42 lt  age gt   SET  str    str       lt  user gt   SET  str    str       lt user gt   SET  str    str          lt firstName gt Sarah lt  firstName gt   SET  str    str          lt lastName gt Brown lt  lastName gt   SET  str    str          lt age gt 23 lt  age gt   SET  str    str       lt  user gt   SET  str    str     lt  users gt    DECLARE  xml xml SELECT  xml   CAST CAST  str AS VARBINARY MAX   AS XML    --  Iterate through each of the  users user  records in our XML SELECT      x Rec query    firstName   value       nvarchar 2000    AS  FirstName       x Rec query    lastName   value       nvarchar 2000    AS  LastName       x Rec query    age   value       int   AS  Age  FROM  xml nodes   users user   as x Rec    And here s the output

User · Answer

Actually you re close to your goal  you just need to use nodes   method to split your rows and then get values   select     s SqmId      m c value   id    varchar max    as id      m c value   type    varchar max    as type      m c value   unit    varchar max    as unit      m c value   sum    varchar max    as  sum       m c value   count    varchar max    as  count       m c value   minValue    varchar max    as minValue      m c value   maxValue    varchar max    as maxValue      m c value       nvarchar max    as Value      m c value   text    1     nvarchar max    as Value2 from sqm as s     outer apply s data nodes  Sqm Metrics Metric   as m c    sql fiddle demo

User · Answer

I ve been trying to do something very similar but not using the nodes  However  my xml structure is a little different   You have it like this    lt Metrics gt       lt Metric id  TransactionCleanupThread RefundOldTrans  type  timer      gt    If it were like this instead    lt Metrics gt       lt Metric gt           lt id gt TransactionCleanupThread RefundOldTrans lt  id gt           lt type gt timer lt  type gt                                  Then you could simply use this SQL statement   SELECT     Sqm SqmId      Data value    Sqm Metrics Metric id  1     varchar max    as id      Data value    Sqm Metrics Metric type  1     varchar max    AS type      Data value    Sqm Metrics Metric unit  1     varchar max    AS unit      Data value    Sqm Metrics Metric sum  1     varchar max    AS sum      Data value    Sqm Metrics Metric count  1     varchar max    AS count      Data value    Sqm Metrics Metric minValue  1     varchar max    AS minValue      Data value    Sqm Metrics Metric maxValue  1     varchar max    AS maxValue      Data value    Sqm Metrics Metric stdDeviation  1     varchar max    AS stdDeviation  FROM Sqm   To me this is much less confusing than using the outer apply or cross apply   I hope this helps someone else looking for a simpler solution

[sql-server] How to query for Xml values and attributes from table in SQL Server?

Examples related to sql-server

Examples related to xml

Examples related to sql-server-2005

Examples related to sql-server-2008-r2

Examples related to sqlxml