Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2003
    Posts
    23

    Unanswered: Unable to custom format my XML with For XML Explicit

    Hi,
    Is it possible to format any kind of XML file with the FOR XML EXPLICIT mode in SQL Server 2005? I am asking because I am trying to format my file in a way that I could get 2 or 3 elements on my parent or child level and put some attributes (icon path for example) in these 2nd element or 3rd element. I am unable to format it that way. Let me show you the way that I would like to have my XML file formated :

    <?xml version="1.0" encoding="UTF-8" ?>
    <rows>
    <row id="14" sort="parent">
    <cell icon="../../images/TreeGrid/folder.gif">Operations</cell>
    <row id="14.13" sort=”child”>
    <cell icon="../../images/TreeGrid/Red.gif">% SP</cell>
    <row id="14.13.1" sort=”sub-child”>
    <cell icon="../../images/TreeGrid/Red.gif">% SP Sub</cell>


    I want to have all my parent, children, sub-children to have these attribut but I am having difficulty putting one in the “Cell” tags. And I really think that my problem resides in the specifying of my column and their directives but I do not know how to fix it. See my icon path is commented here. I have to put the [row!2!cell!xmltext], but it is not working.

    Here is the way that I am building my Select query.

    SELECT
    1 as tag,
    NULL as parent,
    NULL AS 'rows!1!', ----Root rows tag
    ID_metric as [row!2!id],
    Description_english as [row!2!cell!Element],
    ----Icon as [row!2!cell!xmltext],
    Sort as [row!2!sort],
    ID_metric as [row!3!id],
    Description_english as [row!3!cell!Element],
    --Icon as [row!3!cell!xmltext],
    sort as [row!3!sort],
    ID_metric as [row!4!id],
    Description_english as [row!4!cell!Element],
    --Icon as [row!4!cell!xmltext],
    sort as [row!4!sort],
    ID_metric as [row!5!id],
    Description_english as [row!5!cell!Element],
    --Icon as [row!5!cell!xmltext],
    sort as [row!5!sort],
    FROM #buildData
    WHERE HLevel = 0
    UNION ALL
    SELECT
    2 as tag,
    1 as parent,
    NULL AS 'rows!1!',
    ID_metric as [row!2!id],
    Description_english as [row!2!cell!Element],
    --Icon as [row!2!cell!xmltext],
    sort as [row!2!sort],
    ID_metric as [row!3!id],
    Description_english as [row!3!cell!Element],
    --Icon as [row!3!cell!xmltext],
    sort as [row!3!sort],
    ID_metric as [row!4!id],
    Description_english as [row!4!cell!Element],
    --Icon as [row!4!cell!xmltext],
    sort as [row!4!sort],
    ID_metric as [row!5!id],
    Description_english as [row!5!cell!Element],
    --Icon as [row!5!cell!xmltext],
    sort as [row!5!sort],
    FROM #buildData
    WHERE HLevel = 1
    UNION ALL
    SELECT
    3 as tag,
    2 as parent,
    NULL AS 'rows!1!',
    ID_metric as [row!2!id],
    Description_english as [row!2!cell!Element],
    --Icon as [row!2!cell!xmltext],
    sort as [row!2!sort],
    ID_metric as [row!3!id],
    Description_english as [row!3!cell!Element],
    --Icon as [row!3!cell!xmltext],
    sort as [row!3!sort],
    ID_metric as [row!4!id],
    Description_english as [row!4!cell!Element],
    --Icon as [row!4!cell!xmltext],
    sort as [row!4!sort],
    ID_metric as [row!5!id],
    Description_english as [row!5!cell!Element],
    --Icon as [row!5!cell!xmltext],
    sort as [row!5!sort],
    FROM #buildData
    WHERE HLevel = 2
    UNION ALL
    SELECT
    4 as tag,
    3 as parent,
    NULL AS 'rows!1!',
    ID_metric as [row!2!id],
    Description_english as [row!2!cell!Element],
    --Icon as [row!2!cell!xmltext],
    sort as [row!2!sort],
    ID_metric as [row!3!id],
    Description_english as [row!3!cell!Element],
    --Icon as [row!3!cell!xmltext],
    sort as [row!3!sort],
    ID_metric as [row!4!id],
    Description_english as [row!4!cell!Element],
    --Icon as [row!4!cell!xmltext],
    sort as [row!4!sort],
    ID_metric as [row!5!id],
    Description_english as [row!5!cell!Element],
    --Icon as [row!5!cell!xml],
    sort as [row!5!sort],
    FROM #buildData
    WHERE HLevel = 3
    ORDER BY [row!2!sort], [row!3!sort], [row!4!sort], [row!5!sort]
    for XML explicit

    My actual XML Result is this :

    <rows>
    <row id="14" sort="parent">
    <cell>% SP</cell>
    <row id="14.13" sort="child">
    <cell>% SP </cell>
    <row id="14.13.1" sort="child">
    <cell>% SP cild </cell>

    Is it possible to customized anything with For XML Explicit?
    Anybody that can help my with that or indicate me where I am doing wrong? Thanks a lot.

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    don't use for xml explicit if you are on 2005. for xml explicit is an abomination.

    use "for xml path" instead, it's MUCH MUCH MUCH nicer.

  3. #3
    Join Date
    Sep 2003
    Posts
    23
    I know, I have read about it. And I am pretty sure that it is more efficient and simple. But since I am short on time and I am not alone in this project I have to think fast. Last month, I was building a simple XML file in a C# function but since they want to add more sub-sub-sub child I need to do it...more efficiently. And I thought that by doing it SQL it would have been simplier. Not in my case.

    I have to loop with the WITH (CTE command) and then add my SELECT query with the FOR XML EXPLICIT Mode at the end. I tried it with the FOR XML PATH and have not found the equivalent of looping and constructing my XML with the PATH mode.

    Here is my code :

    WITH Hierarchy(ID, Sort, ReportTo, HLevel, SortCol)
    AS
    (
    SELECT Parent.ID as ID,
    Parent.Sort,
    Parent.ReportTo,
    0,
    CAST(Parent.ID AS varbinary(max)) as SortCol
    FROM dbo.fn_build_tree_TEMP(10,1) Parent
    WHERE Parent.ID in (142) ---test 142 first
    UNION ALL
    SELECT Child.ID_Metric as ID,
    Child.Sort,
    Child.ReportTo,
    Parent.HLevel + 1,
    CAST(SortCol + CAST(Child.ID AS binary(4)) AS varbinary(max)) as SortCol
    FROM dbo.fn_build_tree_TEMP(10,1) Child ---test 10 and 1 first
    INNER JOIN Hierarchy Parent
    ON Child.ReportTo = Parent.ID
    )

    And the select Union all XML EXPLICIT come here....

    Hope you understand my problem and limitations?
    Last edited by Jayeff; 04-16-08 at 13:26.

  4. #4
    Join Date
    Sep 2003
    Posts
    23
    Finally, you convinced me to go back read the XML PATH stuff and...find a way to format it to my purpose! It is working....Yesssssss! Thanks!

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    my silence convinced you? nice.

  6. #6
    Join Date
    Sep 2003
    Posts
    23
    They say that talking comes by nature, silence by wisdom...and now I can say that every path has its puddle...especially the explicit one! Cheers!

  7. #7
    Join Date
    Sep 2003
    Posts
    23

    Why can I use CDATA with XML PATH

    ...what a shame! 1 step forward, 1 backward...is it the Microsoft philosophy?

  8. #8
    Join Date
    Sep 2003
    Posts
    23

    Hack for CDATA in XML PATH

    My problem is that I need to pass that XML string into a javascript component and that string may contains a some of "<" or "&" characters...or some latin characters. The way that I have found before in my old C# algorithm to avoid that problem was to put a CDATA section. Otherwise, I am getting errors from my component.

    I thought that I have found a simple hack to my problem by doing this :

    Select
    H.SortCol,
    '<![CDATA[' + min(OwnerShip.[user_name]) + ']]>' as Cdata,
    max(dbo.fn_GetIconsCombinaison(MR.Missed_Target, MR.AP_Icon)) as Icon,

    But I am getting that result :

    .
    .
    .
    <userdata name="tip_0">&lt;![CDATA[Marie Anna & Mike Kay]]&gt;</userdata>
    <cell>% SP </cell>
    <row id="44">
    .
    .
    .

    I am getting these &lt and &gt; character. I am still looking for another way to fit that section. If you are getting a better way to do this, please let me know. Thanks for your help!

  9. #9
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    why not just leave out the CDATA business. what's wrong with having &, <, >, etc escaped? any xml parser will handle those escaped chars without problems.

    normally you'd use CDATA for stuff like an unencoded binary stream that may have non-printable chars in it, and other chars that are simply not allowed in xml.

    You don't normally use CDATA for just a simple text field.

  10. #10
    Join Date
    Sep 2003
    Posts
    23

    Non-printable chars in it

    Actually, I have some non-printable chars in it. Carriage return. But I was thinking of including some <BR> in my Select query.

    If it does not work, I have found a little hack where I put the CDATA section by using the REPLACE function. First in put the :
    cast('<![CDATA[' + cast(H.ID_Metric as nvarchar(10)) + min(OwnerShip.[user_name]) + ']]>' as nvarchar(60)) as Cdata,

    ...and putting the result in a string and then doing a...

    select replace(replace(@mystr,'&lt;','<'), '&gt;', '>').

    The format of the XML string look good now.

    Thanks for your help

  11. #11
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    i don't consider cr a non printable char. it has no glyph, but you can print it just fine.

    anyway xml allows only these chars:

    #x9 | #xA | #xD | [#x20-#xD7FF] | [#xE000-#xFFFD] | [#x10000-#x10FFFF]

    so for example, x0 through x8 are not allowed in any xml file, anywhere. (EDIT: except in a CDATA section)

    see: http://www.w3.org/TR/REC-xml/
    Last edited by jezemine; 04-18-08 at 22:39.

  12. #12
    Join Date
    Sep 2003
    Posts
    23

    CDATA with FOR XML PATH

    Just to conclude this post : I have tried without the CDATA section in my XML file but I was getting strange error from my javascript/ajax component. The information between the CDATA is made to populate a tooltips. When omitting the CDATA and doing a 'mouseover' on the tooltips, I am getting a 'freakin' auto-overflow of special characters. The tooltips is increasing in size each time I am hovering the component. On the website of the component they suggest to put the data in between CDATA section. This probably due to the javascript component way of parsing the XML file.

    Thanks for all your suggestion anyway!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •