locked
SQL query that removes the xml (not sure if xml) tags but keeps the format RRS feed

  • Question

  • Dear PowerPivot users,

    I want to extract an information from a custom field (multiple text) in a powerpivot report.
    When I extract the information from the field I get the following result

    <p><span style="text-decoration: line-through;">​I faza: 26.12.206​​&#160;</span>​Pomereno na 12.1.2017</p>

    When I reshape my query as 

    SELECT
    ProjectName,
    CAST((CAST([Planirano vreme fakturisanja (Proba)]  AS XML).query('//text()')) AS NVARCHAR(max)) AS [Planirano vreme fakturisanja (Proba)] 
    FROM [dbo].[MSP_EpmProject_UserView]

    I get a good result

    ​I faza: 26.12.206​​ ​Pomereno na 12.1.2017

    Which is ok, i did remove the tags. But I want it to keep the format that is in the field. This is how it is written on SharePoint

    ​I faza: 26.12.206​​ Pomereno na 12.1.2017

    Does anyone know if it is possible to keep the original format and remove the tags?

    With best regards,
    Petar
     
    Friday, December 9, 2016 12:25 PM

All replies

  • Hello Petar,

    That is HTML, which is in fact XML and the format is as CSS style defined, which is interpreted by web browser.

    But how do you want the presentation Format; in ASCII /Unicode we don't have format options, just plain text?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, December 9, 2016 12:31 PM
  • Hi Olaf,

    Thank you for your response.
    I need this information for the report, so basically I want it in the same format as it is in SharePoint - I believe plain text.
    In other words like this:

    ​I faza: 26.12.206​​ Pomereno na 12.1.2016

    If this is not possible then I guess I will keep my old query.

    Friday, December 9, 2016 12:39 PM
  • If you are using SSRS in SharePoint the you can output HTML without any modification of the data; see Add HTML into a Report (Report Builder and SSRS) and Format Text in a Text Box (Report Builder and SSRS)

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Friday, December 9, 2016 1:15 PM
  • Thank you for your response.
    Unfortunately I am not using SSRS. I am using PowerPivot.
    Is there a way to do this using PP tool?
    Monday, December 12, 2016 8:55 AM
  • Hi PetarB,

    Could you please post the sample data of [dbo].[MSP_EpmProject_UserView] table for further analysis? 

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 15, 2016 8:49 AM
  • Hi Angelia Zhang,

    I am not sure what you mean by sample data.

    I have created a custom text project field and entered some text in the strikethrough format (For example: <ins cite="mailto:Petar%20Bogojevic" datetime="2016-12-15T11:01">There</ins>).
    I only entered this information for one project. So there is no data for this field other than text for one project (In specific this is the text: ​I faza: 26.12.206​​ Pomereno na 12.1.2017

    Hope this helps,
    Petar

    Monday, December 19, 2016 11:36 AM
  • Hi PetarB,

    You can change the string format when you import the data as follows.

    First, You can split the string by space delimiter. Import your data by Query, and click the Edit, you will get the Power Query Edit. Click the split column-> Space->OK, the string will be splitted several parts. 

    Then click the Second and third columns-> click Merge, you Ifaza:26.12.206. 

    Finally, remove the new column. You will get the expected result.

    More details, please review the similar thread.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Wednesday, December 28, 2016 9:19 AM