Create XML result from query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Create XML result from query

Author

Topic

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-10-16 : 09:05:24

Hello all,
I need to create an XML resultset from a stored procedure that
has 2 input parameters and make a SELECT like this:

Select A.FieldA, A.FieldB, B.FieldA
From TableA A Inner Join TableB
ON A.ID = B.ID
Where A.DateRef = @param1
and B.UP = @param2

The XML has a structure like this:

<TagA>@param1</TagA>
<TagB>@param2</TagB>
<TagC>A.FieldA</TagC>
<TagD>A.FieldB</TagD>
<TagE>B.FieldA</TagE>

How can I accomplish this?

Thanks in advance.

Luis

PS
I'm using SQL Server 2008 R2

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-16 : 09:21:05

[code]SELECT @param1 AS TagA,
@param2 AS TagB ,
A.FieldA AS TagC,
A.FieldB AS TagD,
B.FieldA AS TagE
FROM TableA A
INNER JOIN TableB ON A.ID = B.ID
WHERE A.DateRef = @param1
AND B.UP = @param2
FOR XML PATH('');[/code]You might want to add a non-empty path specifier and perhas a root node. If you want to add a root node, add ", ROOT('YourRootName')" before the semi-colon on the last line.

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-10-17 : 04:10:01

Thanks a lot James, I'll try.
I'll let you know.

Luis

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-10-17 : 05:09:37

Hi James,
I have a problem.
For one tag - for example for the first one - I have to insert some
attributes, that are fields from one or more tables.

Something like this:

<TagA STARTDATE = 'A.StartDate' ENDDATE = 'A.EndDate' PLANTID = B.PlantID />
<TagB>@param2</TagB>
<TagC>A.FieldA</TagC>
<TagD>A.FieldB</TagD>
<TagE>B.FieldA</TagE>

Is it possible?

Luis

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-10-17 : 06:06:12

Another little adding.
I have write in this way:

...my Select
FOR XML PATH('EDIT'), ROOT('FLUX')

Now I need to insert the namespace in root tag, to obtain:

<FLUX xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'>
...

Is it possible?

Luis

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-17 : 08:22:05

Something like


WITH XMLNAMESPACES( 'http://www.w3.org/2001/XMLSchema-instance' as xsi)
SELECT 
A.StartDate  AS 'TagA/@STARTDATE',
A.EndDate  AS 'TagA/@ENDDATE',
B.PlantID AS 'TagA/@PLANTID',
'' AS TagA,
@Param2 AS TagB,
A.FieldA AS TagC,
A.FieldB AS TagD,
B.FieldA AS TagE
FROM ....
FOR XML PATH('EDIT'), ROOT('xsi:FLUX')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-17 : 08:23:25

quote:
Originally posted by Ciupaz

Hi James,
I have a problem.
For one tag - for example for the first one - I have to insert some
attributes, that are fields from one or more tables.

Something like this:

<TagA STARTDATE = 'A.StartDate' ENDDATE = 'A.EndDate' PLANTID = B.PlantID />
<TagB>@param2</TagB>
<TagC>A.FieldA</TagC>
<TagD>A.FieldB</TagD>
<TagE>B.FieldA</TagE>

Is it possible?

Luis

For attributes, use the "@" symbol, for example:

SELECT  @param1 AS TagA,
		A.StartDate as [TagA/@StartDate],
		@param2 AS TagB ,
		A.FieldA AS TagC,
        A.FieldB AS TagD,
        B.FieldA AS TagE
FROM    TableA A
        INNER JOIN TableB ON A.ID = B.ID
WHERE   A.DateRef = @param1
        AND B.UP = @param2
FOR XML PATH('');

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-17 : 08:24:19

quote:
Originally posted by Ciupaz

Another little adding.
I have write in this way:

...my Select
FOR XML PATH('EDIT'), ROOT('FLUX')

Now I need to insert the namespace in root tag, to obtain:

<FLUX xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'>
...

Is it possible?

Luis

Yes, use XMLNAMESPACES

;WITH XMLNAMESPACES  (DEFAULT 'http://www.w3.org/2001/XMLSchema-instance')
SELECT
... rest of the query

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-10-17 : 08:38:00

Thank you very much Visakh and James.

Luis

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources