Link to home
Create AccountLog in
Avatar of IOSH
IOSH

asked on

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?
Avatar of Alpesh Patel
Alpesh Patel
Flag of India image

XML is case sensitive
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?
Avatar of IOSH
IOSH

ASKER

Thanks for your responses, apologies just not got around to reveiwing them yet. Will be back soon
Avatar of IOSH

ASKER

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
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.
I am afraid I do not have time to do this based on your Xml data.
ASKER CERTIFIED SOLUTION
Avatar of IOSH
IOSH

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of IOSH

ASKER

.
The link I provided gave the solution with the correct syntax (later confirmed by the authors own "solution").
Avatar of IOSH

ASKER

The code in the last comment by me solved the problem