January 26, 2009 at 5:22 pm
Hello All,
Here's a big request for everyone... I'm charged with selecting a bunch of data into a specific xml format with multiple elements. I'm fine when I have to select a single rowset into an element, but I can't find any help selecting into elements within elements.
I have a table:
CREATE TABLE [dbo].[Location](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Location_Number] [varchar](5) NOT NULL,
[Location_Name] [varchar](50) NULL,
[Address] [varchar](50) NOT NULL,
[City] [varchar](50) NOT NULL,
[State] [varchar](2) NOT NULL,
[Zip] [varchar](9) NOT NULL,
[Phone] [varchar](11) NOT NULL,
[Latitude] [float] NULL,
[Longitude] [float] NULL
I need to select the columns into the attached format. Can anyone help out or point me in the right direction? I'd greatly appreciate it!
Thanks in advance,
Ruprecht
January 27, 2009 at 6:05 pm
Ok, I was able to get it to work using the FOR XML EXPLICIT clause. Everything I read said to name the column as [marker!1!infowindow!cdata], but I couldn't get it to work. Here is what I came up with (xml_solution.txt)
January 27, 2009 at 6:13 pm
One last thing I'm having problems with... When I try to create my SELECT statement into a stored procedure, it gives me an incorrect syntax error. Is there a limitation on using FOR XML to return a result set in a stored proc?
January 28, 2009 at 9:47 am
Yes, you can create hierarchical nested XML (e.g., Master / detail) with elements and attributes with SQL Server 2005. The trick is to use sub-selects where you want the nested XML. See the attached example. Also see the BOL and other web articles.
It works very well as that is how we are returning data back to the middle tier (BLL) of our web-based application.
As for returning XML back from a stored procedure use an OUTPUT argument vs. a single row, single column result set.
Example:
CREATE PROCEDURE foo
(
@input1 ...,
@xml_out xml OUTPUT
)
AS
SET NOCOUNT ON; -- Don't return counts of rows affected.
BEGIN;
SELECT @xml_out =
(
SELECT
whatever...
FOR XML PATH ('RootElementName'), TYPE
);
END;
January 28, 2009 at 2:20 pm
Fantastic! Thanks for your help. Everything is working perfectly now. If anyone needs it, this is the xml format to pass to the google maps api.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply