How to query values from xml nodes

Question

i have a table that contains an XML column   CREATE TABLE Batches      BatchID int     RawXml xml      The xml contains items such as    lt GrobReportXmlFileXmlFile gt      lt GrobReport gt          lt ReportHeader gt             lt OrganizationReportReferenceIdentifier gt 1 lt  OrganizationReportReferenceIdentifier gt             lt OrganizationNumber gt 4 lt  OrganizationNumber gt          lt  ReportHeader gt     lt  GrobReport gt      lt GrobReport gt          lt ReportHeader gt             lt OrganizationReportReferenceIdentifier gt 2 lt  OrganizationReportReferenceIdentifier gt             lt OrganizationNumber gt 4 lt  OrganizationNumber gt          lt  ReportHeader gt     lt  GrobReport gt      lt GrobReport gt          lt ReportHeader gt             lt OrganizationReportReferenceIdentifier gt 3 lt  OrganizationReportReferenceIdentifier gt             lt OrganizationNumber gt 4 lt  OrganizationNumber gt          lt  ReportHeader gt     lt  GrobReport gt      lt GrobReport gt          lt ReportHeader gt             lt OrganizationReportReferenceIdentifier gt 4 lt  OrganizationReportReferenceIdentifier gt             lt OrganizationNumber gt 4 lt  OrganizationNumber gt          lt  ReportHeader gt     lt  GrobReport gt      What i want is to generate a set  that contains   OrganizationReportReferenceNumber  OrganizationNumber                                                       1                                  4 2                                  4 3                                  4 4                                  4   i ve tried   SELECT      foo value   ReportHeader OrganizationReportReferenceIdentifier   AS ReportIdentifierNumber      foo value   ReportHeader OrganizationNumber   AS OrginazationNumber FROM CDRBatches RawXML query   GrobReportXmlFileXmlFile GrobReport ReportHeader   foo   but that doesn t work  i ve tried   SELECT      foo value   ReportHeader OrganizationReportReferenceIdentifier   AS ReportIdentifierNumber      foo value   ReportHeader OrganizationNumber   AS OrginazationNumber FROM RawXML nodes   GrobReportXmlFileXmlFile GrobReport ReportHeader   bar foo    But that doesn t work  The XPath expression   GrobReportXmlFileXmlFile GrobReport ReportHeader   is correct  in any other xml system it returns    lt ReportHeader gt       lt OrganizationReportReferenceIdentifier gt 1 lt  OrganizationReportReferenceIdentifier gt       lt OrganizationNumber gt 4 lt  OrganizationNumber gt   lt  ReportHeader gt   lt ReportHeader gt       lt OrganizationReportReferenceIdentifier gt 2 lt  OrganizationReportReferenceIdentifier gt       lt OrganizationNumber gt 4 lt  OrganizationNumber gt   lt  ReportHeader gt   lt ReportHeader gt       lt OrganizationReportReferenceIdentifier gt 3 lt  OrganizationReportReferenceIdentifier gt       lt OrganizationNumber gt 4 lt  OrganizationNumber gt   lt  ReportHeader gt   lt ReportHeader gt       lt OrganizationReportReferenceIdentifier gt 4 lt  OrganizationReportReferenceIdentifier gt       lt OrganizationNumber gt 4 lt  OrganizationNumber gt   lt  ReportHeader gt    So  it s obvious from my queries that i d like to see  After reading a dozen Stackover questions and answers  i m no closer to solving the problem

User · Answer

This works  been tested     SELECT  n c value  OrganizationReportReferenceIdentifier 1    varchar 128    AS  OrganizationReportReferenceNumber             n c value   OrganizationNumber  1    varchar 128    AS  OrganizationNumber  FROM    Batches t Cross   Apply RawXML nodes   GrobXmlFile Grob ReportHeader   n c

User · Answer

Try this   SELECT RawXML value    GrobXmlFile  Grob  ReportHeader  OrganizationReportReferenceIdentifier node    1    varchar 50    AS ReportIdentifierNumber         RawXML value    GrobXmlFile  Grob  ReportHeader  OrganizationNumber node    1    int   AS OrginazationNumber FROM Batches

User · Answer

if you have only one xml in your table  you can convert it in 2 steps   CREATE TABLE Batches      BatchID int     RawXml xml     declare  xml xml  select top 1 RawXml from  Batches   SELECT  --b BatchID          x XmlCol value   ReportHeader OrganizationReportReferenceIdentifier  1    VARCHAR 100    AS OrganizationReportReferenceIdentifier          x XmlCol value   ReportHeader OrganizationNumber  1    VARCHAR 100    AS OrganizationNumber FROM     xml nodes   CasinoDisbursementReportXmlFile CasinoDisbursementReport   x XmlCol

User · Answer

SELECT  b BatchID          x XmlCol value   ReportHeader OrganizationReportReferenceIdentifier  1    VARCHAR 100    AS OrganizationReportReferenceIdentifier          x XmlCol value   ReportHeader OrganizationNumber  1    VARCHAR 100    AS OrganizationNumber FROM    Batches b CROSS APPLY b RawXml nodes   CasinoDisbursementReportXmlFile CasinoDisbursementReport   x XmlCol     Demo  SQLFiddle

[sql-server] How to query values from xml nodes?

Examples related to sql-server

Examples related to sql-server-2005

Examples related to xpath

Examples related to sqlxml