Nesting xml

Question

Nesting xml

lmnorms

SSC Enthusiast

Points: 140
More actions
April 1, 2014 at 8:32 am

#146867

Hello,
I am working on xml from SS2k12 results. I can get it to work with for xml auto; elements but I want to enhance the sql to produce:
<Demographics>
<EID>019999</EID>
<Phone>
<number>+1 (202) 333-4568</number>
</Phone>
</Demographics>
I am trying this (which I believe is close):
select b.employeeid as EID
,(
Select a.CellPhone as number
from tb_ci_employees a
where a.EmployeeID in ('019999')
For XML path('Cell_phone'), type [Does not work]
)
from tb_ci_employees b
where b.EmployeeID in ('019999')
for xml path('EE'), Root('Demographics') [Does not work]
I can get this to work:
<a>
<EID>019999</EID>
<number>+1 (202) 333-4568</number>
</a>
With:
select a.employeeid as EID
,a.CellPhone as number
from tb_ci_employees a
where a.EmployeeID in ('019999')
For XML AUTO, Elements
I am unsure of how to insert a nesting around a.cell in Auto mode.
Any advice would be appreciated. How do I get either to work like the first xml that I want to model.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

You mention SS2012 in your post, yet this is a 2005 forum, which is a bit confusing.

If you could post some source data in readily consumable format (see the link in my signature for details on how to do this), you will be more likely to get a working solution.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 2

Please post proper DDL and enough data, the problem is straight forward from there.

lmnorms SSC Enthusiast Points: 140 More actions · Answer 3

Sorry for posting in the wrong forum. I can move it if necessary.

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 4

lmnorms (4/1/2014)
Sorry for posting in the wrong forum. I can move it if necessary.

No worries, but do you have any sample data and table structure? Makes it easier to help:cool:

lmnorms SSC Enthusiast Points: 140 More actions · Answer 5

Hello Everyone,

Thank you for all your time. I figured out. I am posting the solution I found in hopes it will help in future.

Select (

SELECT p.EmployeeID AS Employeeid,

--,

(

SELECT

a.CellPhone AS number

FROM

Db_Utils.dbo.tb_tst_Employees_upd a

WHERE

a.EmployeeID = p.EmployeeID

FOR

XML PATH('mobile_Phone'),

TYPE

) AS mobile_Phone

FROM Db_Utils.dbo.tb_tst_Employees_upd p

WHERE p.EmployeeID IN ('000001', '019999')

FOR XML path('Row'), Type

)

FOR XML Path('Employee_List')

Difficult concept, easy once it is understood.

Please close thread.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

Eirikur Eiriksson (4/1/2014)
Please post proper DDL and enough data, the problem is straight forward from there.

If you're going to tell people that, then you need to tell them what "proper DDL and enough data" actually means. I recommend you provide them with the first link under "Helpful Links" in my signature line below. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mickyT SSChampion Points: 10360 More actions · Answer 7

Hi

Because I've had to study up on this lately:w00t:, you could also try

SELECT EmployeeID AS 'Employeeid',

CellPhone AS 'mobile_Phone/Number'

FROM Db_Utils.dbo.tb_tst_Employees_upd p

WHERE p.EmployeeID IN ('000001', '019999')

FOR XML Path('Row'), ROOT('Employee_List');

lmnorms SSC Enthusiast Points: 140 More actions · Answer 8

Ok. One last question: I need to get the line '<?xml version="1.0"?>' in the top of the results before any xml. How can I do that using the statement I have coded?

Thank you in advance for your time.

lmnorms.

Should look like this:

<?xml version="1.0"?>

<employee_list>

...

mickyT SSChampion Points: 10360 More actions · Answer 9

lmnorms (4/2/2014)
Ok. One last question: I need to get the line '<?xml version="1.0"?>' in the top of the results before any xml. How can I do that using the statement I have coded?
Thank you in advance for your time.
lmnorms.
Should look like this:
<?xml version="1.0"?>
<employee_list>
...

Here's a couple of similar questions that should help you

http://www.sqlservercentral.com/Forums/Topic1533730-392-1.aspx

http://stackoverflow.com/questions/9002403/how-to-add-xml-encoding-xml-version-1-0-encoding-utf-8-to-xml-output-in

Also there is a series of Stairway articles that are always helpful

Stairway to XML[/url]

Edit: added stairway

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 10

Jeff Moden (4/1/2014)
Eirikur Eiriksson (4/1/2014)
Please post proper DDL and enough data, the problem is straight forward from there.
If you're going to tell people that, then you need to tell them what "proper DDL and enough data" actually means. I recommend you provide them with the first link under "Helpful Links" in my signature line below. 😉

Thanks, point taken 😎

lmnorms SSC Enthusiast Points: 140 More actions · Answer 11

Thank you. Adding in:

Select '<?xml version="1.0"?>' + Cast((

[Original Select

]

) AS VARCHAR(MAX)) as XMLData

Worked nicely. Thank you for the information.

Warning: if you are used to clicking on the results and opening the xml into the formated view. It won't after this change. Because it puts in xml data format which behaves more like a string. Workaround Copy results or save to a file as xml. Then open up in a browser and walla, the view is like the node tree view.

Thank you again friends for your help. Happy SQL'ing to you.

Phil Parkin SSC Guru Points: 246695 More actions · Answer 12

lmnorms (4/3/2014)
Thank you. Adding in:
Select '<?xml version="1.0"?>' + Cast((
[Original Select
]
) AS VARCHAR(MAX)) as XMLData
Worked nicely. Thank you for the information.
Warning: if you are used to clicking on the results and opening the xml into the formated view. It won't after this change. Because it puts in xml data format which behaves more like a string. Workaround Copy results or save to a file as xml. Then open up in a browser and walla, the view is like the node tree view.
Thank you again friends for your help. Happy SQL'ing to you.

Or CASTing it all back to XML should work, I would think.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

lmnorms SSC Enthusiast Points: 140 More actions · Answer 13

Hello,

I have a question about the nesting xml. Now, I want to deliver 150 rows at a time.

How can I restrict the query I have come up with to only records 1-150 then 150 to 300.

Thank you in advance.

lmnorms SSC Enthusiast Points: 140 More actions · Answer 14

Sorry...But I found what will work is:

inside at my root select putting in the clause:

OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY

This will limit my results. I can change the number to what I need.

Thanks again.