SQL query with simple XML Name and Value

Hi,

I am using SQL server 2005 and I need to produce some XML from a SQL database in the format

<?xml version="1.0" encoding="ISO-8859-1"?>
<Wallboard_Doc>
   <Values>
      <Value>
         <Name>New Members Today</Name>
         <Value>2</Value>
      </Value>
      <Value>
         <Name>Current Members</Name>
         <Value>12522</Value>
      </Value>
   </Values>
</Wallboard_Doc>

I also need the Wallboard_doc tags
Can someone help with this?
IOSHAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)Commented:
XML is case sensitive
Anthony PerkinsCommented:
Try posting a script to load some tables with sample data and a query that outputs the desired result, we can then "translate" that to the required Xml.

>>I also need the Wallboard_doc tags <<
Care to elaborate?
IOSHAuthor Commented:
Thanks for your responses, apologies just not got around to reveiwing them yet. Will be back soon
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

IOSHAuthor Commented:
Hi all

we are trying to extract the information from SQL into a specific format for a wall board software (OAK).  We are trying to publish soem membership stats.

 The XML feed requires the following structure

<Wallboard_Doc>
<Values>
<Value>
<Name>Members</Name>
<Value>39685</Value>
</Value>
</Values>
</Wallboard_Doc>

The Name = name to be displayed n wall board
Value = value to be displayed  which is being derived from the SQL server.

the svery simple script currently being played with is as -

select  Count (MEP_STATUS) as 'Value'
from Member_period
WHERE mep_status BETWEEN '10-Renewal Created' and '22-Current'
for xml PATH('Values'),
ROOT('Wallboard_Doc')
GO 

Open in new window


This produces one value which we intended to output and refresh. The question is how to insert the nodes and the root node.

Any assistance will be greatly received
Anthony PerkinsCommented:
What I meant by "posting a script to load some tables with sample data and a query that outputs the desired result" was a CREATE TABLE statement, plus a series of INSERT statements to add the data and finally (if not obvious) a query that outputs the data.
Anthony PerkinsCommented:
I am afraid I do not have time to do this based on your Xml data.
IOSHAuthor Commented:
Sorry for the delay in responding, I have now got a solution the code used is below in case anyone is interested

Select 'Current Members' as 'Value/Name'
, Count (1) as 'Value/Value'
from Member_period
WHERE mep_status BETWEEN '10-Renewal Created' and '22-Current'
for xml path ('Values'), root('Wallboard_Doc')

IOSH

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IOSHAuthor Commented:
.
simonpaul64Commented:
The link I provided gave the solution with the correct syntax (later confirmed by the authors own "solution").
IOSHAuthor Commented:
The code in the last comment by me solved the problem
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.