September 29, 2014 at 1:38 pm
Hello,
I am learning XML and would like to show the type attribute in my XML output. Below is my query, current results, and desired results:
Query:
SELECT #TempG.Location, #Temp.Building as Building, #Temp.RoomName
FROM #Temp
where LocID='123'
for xml raw ('Location'), ROOT ('Locations');
Current Results:
<Locations>
<Location>
<LocationName>USA</LocationName>
<Building>White House</Building>
<RoomName>Oval Office</RoomName>
</Location>
</Locations>
Desired Results:
<Locations type="group">
<Location type="group">>
<LocationName type="text">>USA</LocationName>
<Building type="text">>White House</Building>
<RoomName type="text">>Oval Office</RoomName>
</Location>
</Locations>
How can I show the type the tag is? In this example I need to show group and text.
Thank you in advance!
September 29, 2014 at 1:57 pm
Quick suggestion to get you started
😎
USE tempdb;
GO
SET NOCOUNT ON;
;WITH SAMPLE_DATA(Location,Building,RoomName,Habitant )AS
(SELECT * FROM (VALUES
('USA','White House','Oval Office','Mickey Mouse')
,('UK','Whitehall','24hour Lounce','Donald Duck')
) AS X(Location,Building,RoomName,Habitant)
)
SELECT
'group' AS '@type'
,'text' AS 'Location/@type'
,SD.Location
,SD.Habitant AS 'Building/@habitant'
,SD.Building
,SD.RoomName
FROM SAMPLE_DATA SD
FOR XML PATH('Location'),ROOT('Locations');
Results
<Locations>
<Location type="group">
<Location type="text">USA</Location>
<Building habitant="Mickey Mouse">White House</Building>
<RoomName>Oval Office</RoomName>
</Location>
<Location type="group">
<Location type="text">UK</Location>
<Building habitant="Donald Duck">Whitehall</Building>
<RoomName>24hour Lounce</RoomName>
</Location>
</Locations>
September 29, 2014 at 2:08 pm
Thank you very much!
September 30, 2014 at 8:47 am
Hi,
Also, how can I do nesting?
October 4, 2014 at 1:11 pm
Meatloaf (9/30/2014)
Hi,Also, how can I do nesting?
Here is a quick nesting example
😎
USE tempdb;
GO
SET NOCOUNT ON;
;WITH SAMPLE_DATA(Location,Building,RoomName,Habitant )AS
(SELECT * FROM (VALUES
('USA','White House','Oval Office','Mickey Mouse')
,('UK','Whitehall','24hour Lounce','Donald Duck')
) AS X(Location,Building,RoomName,Habitant)
)
,LOCATION_DETAIL(Location,LocationType) AS
(SELECT * FROM (VALUES
('USA','Condinental')
,('UK','Island')
) AS X(Location,LocationType)
)
SELECT
'group' AS '@type'
,'text' AS 'Location/@type'
,(SELECT LD.LocationType FROM LOCATION_DETAIL LD
WHERE LD.Location = SD.Location) AS 'Location/Detail'
,SD.Location
,SD.Habitant AS 'Building/@habitant'
,SD.Building
,SD.RoomName
FROM SAMPLE_DATA SD
FOR XML PATH('Location'),ROOT('Locations');
Results
<Locations>
<Location type="group">
<Location type="text">
<Detail>Condinental</Detail>USA</Location>
<Building habitant="Mickey Mouse">White House</Building>
<RoomName>Oval Office</RoomName>
</Location>
<Location type="group">
<Location type="text">
<Detail>Island</Detail>UK</Location>
<Building habitant="Donald Duck">Whitehall</Building>
<RoomName>24hour Lounce</RoomName>
</Location>
</Locations>
October 23, 2014 at 2:36 pm
How is it possible to export this to a XML file? Do I need to use BCP and cmodify the code into a string?
October 23, 2014 at 10:33 pm
Meatloaf (10/23/2014)
How is it possible to export this to a XML file? Do I need to use BCP and cmodify the code into a string?
There are quite few ways, here is a bcp example
😎
bcp "(the XML Query)" queryout TheOutputFileName.xml -w -S(Server Name) -d(Database Name) -T
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply