Thread: XML Output

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Posts
    4
    Rep Power
    0

    XML Output


    Hi,

    I have posted this in the SQL forum however plenty of views and no replies so I am hoping someone here may be able to shed some light.

    I have been trying to tailor a query which will return XML data back ready for use with the 'fusion charts'.

    I have so far been 90% successful but a certain additional piece of info needs to be added for this to completely work.

    the goal is for the result to appear as follows:

    <chart caption='Calls Closed Today'
    xAxisName='Specialist' yAxisName='Closed'>
    <Set label="JOEB" Value="1" />
    <Set label="JANEB" Value="2" />
    <Set label="JACKB" Value="1" />
    <Set label="JILLB" Value="2" />
    <Set label="JOHNB" Value="5" />
    <Set label="JULIEB" Value="5" />
    <Set label="JAMESB" Value="5" />
    </chart>

    Now, by running the following query:

    SELECT SpecialistID AS label, COUNT(1) AS Value FROM dbo.Request
    WHERE ResolutionActual >= (DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())))
    AND SpecialistID IN ('JOEB', 'JANEB', 'JACKB', 'JILLB', 'JOHNB', 'JULIEB', 'JAMESB')
    GROUP BY SpecialistID
    FOR XML RAW ('set'), ROOT ('chart');

    This produces the following result:

    <chart>
    <Set label="JOEB" Value="1" />
    <Set label="JANEB" Value="2" />
    <Set label="JACKB" Value="1" />
    <Set label="JILLB" Value="2" />
    <Set label="JOHNB" Value="5" />
    <Set label="JULIEB" Value="5" />
    <Set label="JAMESB" Value="5" />
    </chart>

    The item I am missing (as you will see) is the additional code stored within the <chart> element.

    How am I able to work this code in? I have briefly looked at EXPLICIT XML output which is more controllable but there is little out there which would assist me with this particular instance.

    I would appreciate any help given,
    Thanks!
  2. #2
  3. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7170
    FOR XML RAW ('set'), ROOT ('chart');
    I'm pretty sure this is not "standard" SQL. What database server / version are you using?
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Posts
    4
    Rep Power
    0
    Actually that is a perfectly acceptable RAW XML output,

    FOR XML RAW ('set'), < here I am re-labelling the rows to display as 'set'.

    ROOT ('chart');

    the default for 'root' is to appear as <root> xml_code </root>

    I have simply specified this to appear as 'chart'.

    My guess is that RAW output may not be the way forward for me, it may be that EXPLICIT or PATH might need to be used but that involves re-writing everything and there is suprisingly small ammount of information about on this subject.

    EDIT: to answer your question - MS SQL server 2008
  6. #4
  7. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7170
    I was able to infer what it meant, but that doesn't mean it's standard SQL. Your previous thread was posted in the MySQL forum, and since MySQL doesn't have anything like "FOR XML" it's not surprising that you didn't get any replies there.

    I've never used MSSQL before, but some googling revealed that you might be able to do it with a nested query:
    http://www.sqlservercentral.com/Foru...5934-21-1.aspx
    select getDate() as '@timestamp',
    ( select name as '@name', xtype as 'data/@xtype'
    from sysobjects
    for xml path('object'),type
    )
    for xml path('root')
    <root timestamp="july 25 2009" xmlns:h="http://www.w3.org/TR/html4/">
    <object name="sysrowsetcolumns">
    <data xtype="S" />
    </object>
    </root>
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around

IMN logo majestic logo threadwatch logo seochat tools logo