locked
XML Parsing RRS feed

  • Question

  •  Hi All

    I am running the query;

    Select  
    QT.[CustomerCode],
    QT.[Customer] as [CustomerName],
    QT.ReportType,
    QT.Actual_Or_Expected,
    (select Stuff((Select ', [Customer].[' + a.Customer + '].[' + a.CustomerSiteName + ']'
          From dwh.report_client_configuration_QuarterQuantity a where a.Customer+Actual_or_Expected=QT.Customer+QT.Actual_Or_Expected
          For XML Path('')),1,2,'')) as Sites from (
    select distinct [CustomerCode], [Customer], ReportType, Actual_Or_Expected from dwh.report_client_configuration_QuarterQuantity)QT

    That works brilliantly but if my CustomerSiteName has an & in it - the output looks as follows:

    [Customer].[London Sch Of Hyg & Trop Med].[LONDON SCH OF HYG & TROP MED]

    How can i display the & sign correctly from the name and get rid of the &amp and just Display it like this:

    [Customer].[London Sch Of Hyg & Trop Med].[LONDON SCH OF HYG & TROP MED]

    Regards

    James

    Tuesday, November 5, 2013 6:47 PM

Answers

  • The .value method of the xml datatype can de-entitize the xml correctly.  Try this:

    Select  
     QT.[CustomerCode], 
     QT.[Customer] as [CustomerName], 
     QT.ReportType, 
     QT.Actual_Or_Expected,
     (select Stuff((Select ', [Customer].[' + a.Customer + '].[' + a.CustomerSiteName + ']'
           From @dwh_report_client_configuration_QuarterQuantity a where a.Customer+Actual_or_Expected=QT.Customer+QT.Actual_Or_Expected
           For XML Path(''),type).value('.','nvarchar(max)'),1,2,'')) as Sites from (
     select distinct [CustomerCode], [Customer], ReportType, Actual_Or_Expected from @dwh_report_client_configuration_QuarterQuantity) QT

    • Proposed as answer by Naomi N Wednesday, November 6, 2013 2:12 AM
    • Marked as answer by Allen Li - MSFT Thursday, November 14, 2013 1:40 PM
    Tuesday, November 5, 2013 11:22 PM

All replies

  • How can i display the & sign correctly from the name and get rid of the &amp and just Display it like this:

    Hello James,

    The ampersand & is a reserved sign in XML, therefore it will be encoded with &amp; same with ">" = &gt; and "<" = &lt and several signs more. You can't change this, it's the normal XL encoding.

    Every XML client is able to decode it and show it the right way.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Tuesday, November 5, 2013 6:59 PM
  • Below is the list as per Olaf:


    Character Name

    Entity Reference

    Character Reference

    Ampersand

    &amp;

    &

    Left angle bracket

    &lt;

    Right angle bracket

    &gt;

    Straight quotation mark

    &quot;

    "

    Apostrophe

    &apos;

    '


    Tuesday, November 5, 2013 7:06 PM
  • The .value method of the xml datatype can de-entitize the xml correctly.  Try this:

    Select  
     QT.[CustomerCode], 
     QT.[Customer] as [CustomerName], 
     QT.ReportType, 
     QT.Actual_Or_Expected,
     (select Stuff((Select ', [Customer].[' + a.Customer + '].[' + a.CustomerSiteName + ']'
           From @dwh_report_client_configuration_QuarterQuantity a where a.Customer+Actual_or_Expected=QT.Customer+QT.Actual_Or_Expected
           For XML Path(''),type).value('.','nvarchar(max)'),1,2,'')) as Sites from (
     select distinct [CustomerCode], [Customer], ReportType, Actual_Or_Expected from @dwh_report_client_configuration_QuarterQuantity) QT

    • Proposed as answer by Naomi N Wednesday, November 6, 2013 2:12 AM
    • Marked as answer by Allen Li - MSFT Thursday, November 14, 2013 1:40 PM
    Tuesday, November 5, 2013 11:22 PM
  • You can use the TYPE directive and .value method to preserve the & and other XML control characters.  For example

    Create Table #SampleData(CustomerCode int, CustomerSiteName varchar(20), Customer varchar(20), 
       Actual_Or_Expected char(1), ReportType int);
    Insert #SampleData(CustomerCode, CustomerSiteName, Customer, Actual_Or_Expected, ReportType) Values 
    (1, 'CustomerSiteOne', 'CustomerOne', 'A', 2),
    (2, 'CustomerSiteTwo', 'Tesas A&M', 'A', 2);
    
    Select
    QT.[CustomerCode],
    QT.[Customer] as [CustomerName],
    QT.ReportType,
    QT.Actual_Or_Expected,
    (select Stuff((Select ', [Customer].[' + a.Customer + '].[' + a.CustomerSiteName + ']'
          From #SampleData a where a.Customer+Actual_or_Expected=QT.Customer+QT.Actual_Or_Expected
          For XML Path(''), Type).value('.', 'varchar(max)'),1,2,'')) as Sites from (
    select distinct [CustomerCode], [Customer], ReportType, Actual_Or_Expected from #SampleData)QT
    go
    Drop Table #SampleData;

    Tom

    Tuesday, November 5, 2013 11:37 PM