SQL for XML format header and groups

Hi there,
I'm creating a store procedure that returns simple data pertaining accounts.
What I need to is to format the xml output the following way:

<AccountNum>
    <AccountNum>000117622</AccountNum>
    <Name>                        </Name>
    <Address1>123 Man street      </Address1>
    <MTU>123</MTU>
    	<Port>1</Port>
    	<WaterConsumption4>30.00</WaterConsumption4>
    	<RunDate>2014-11-11T00:00:00</RunDate>
    </MTU>
    <MTU>345</MTU>
    	<Port>2</Port>
    	<WaterConsumption4>30.00</WaterConsumption4>
     	<RunDate>2014-11-11T00:00:00</RunDate>
    </MTU>
  </AccountNum>

Open in new window


here is the store procedure:
alter procedure usp_Summation360Consumption
as 
begin 
SELECT top 100 [AccountNum]
      ,[MTU]
      ,[Port]
      ,[WaterConsumption4]
      ,[Name]
      ,[Address1]
      ,[CutOffdt]
      ,[RunDate]
  FROM Consumption360
--  where AccountNum = '000112182'
for xml path ('AccountNum'), root('Account'), type
  end

Open in new window


How can I modify the procedure to get the output I need?
Thanks,
COHFL
COHFLAsked:
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.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Firstly, you can't. What you have given is not correct XML code.

For instance:

    <MTU>123</MTU>
    	<Port>1</Port>
    	<WaterConsumption4>30.00</WaterConsumption4>
    	<RunDate>2014-11-11T00:00:00</RunDate>
    </MTU>

Open in new window


has got two </MTU> but only one </MTU>

Guessing what you actually mean, I would suggest:

alter procedure usp_Summation360Consumption
as 
begin 
SELECT top 100 [AccountNum]
      ,'' as [Name]
      ,[Address1]
      ,[MTU]
      ,[Port] as 'MTU/Port'
      ,[WaterConsumption4] as 'MTU/WaterConsumption4'
      ,[Name] as 'RunDate' as 'MTU/RunDate'
  FROM Consumption360
--  where AccountNum = '000112182'
for xml path ('AccountNum'), root('Account')
  end

Open in new window

0

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
COHFLAuthor Commented:
not quite there yet!

maybe syntax wise was not accurate but here is what im trying:
SELECT [AccountNum]
      ,[Name]
      ,[Address1]
	  (SELECT [MTU]
			  ,[Port]
			  ,[WaterConsumption4]
			  ,[RunDate]
		  FROM Consumption360
		  where AccountNum = '000100226'
		  order by [MTU], [PORT]
		for xml path ('MTU'), root('METER'), type
		)
  FROM Consumption360
  where AccountNum = '000100226'
  for xml path ('AccountNum'), root('ACCOUNT'), type

Open in new window


the idea is that the account info is wrap around on the top and each MTU after but under the same Account
0
COHFLAuthor Commented:
but yet im getting and error that reads

Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'order'.

Just comment that out to see if it work then complaints on the next line =(
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You are missing a comma at the end of line 3.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
If you can't get it quite right, I suggest posting the structure of the table and some sample data. But how close are you now?
0
COHFLAuthor Commented:
yeah I just notice that =( thanks to point it out.
After I fix that little error on my part I get another error:
Unable to show XML. The following error happened:
Unexpected end of file has occurred. The following elements are not closed: RunDate, MTU, METER, AccountNum, ACCOUNT. Line 1, position 2097154

One solution is to increase the number of characters retrieved from the server for XML data. To change this setting, on the Tools menu, click Options.

I did that and pick the unlimited characters for XML and still does not work =(
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Have you tried my version? How close is that to what you want?
0
COHFLAuthor Commented:
Phil, I have try your version but it does not break on every MTU and repeats the account number on each entry. Im trying to format the output to break on each account but at the same time break on each MTU
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
I suggest posting the structure of the table and some sample data. Any other experts want to help?
0
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.