April 1, 2014 at 8:32 am
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.
April 1, 2014 at 8:47 am
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.
April 1, 2014 at 9:35 am
Please post proper DDL and enough data, the problem is straight forward from there.
April 1, 2014 at 11:28 am
Sorry for posting in the wrong forum. I can move it if necessary.
April 1, 2014 at 12:24 pm
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:
April 1, 2014 at 3:13 pm
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.
April 1, 2014 at 4:29 pm
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
Change is inevitable... Change for the better is not.
April 1, 2014 at 4:40 pm
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');
April 2, 2014 at 7:50 am
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>
...
April 2, 2014 at 11:47 am
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
Also there is a series of Stairway articles that are always helpful
Edit: added stairway
April 2, 2014 at 2:44 pm
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 😎
April 3, 2014 at 9:18 am
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.
April 3, 2014 at 9:40 am
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.
May 8, 2014 at 12:49 pm
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.
May 8, 2014 at 1:40 pm
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.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply