How to convert query result\ resultset into XML an

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 2005 Forums

Transact-SQL (2005)

How to convert query result\ resultset into XML an

Author

Topic

Chr
Starting Member

1 Post

Posted - 2011-07-15 : 18:49:49

Hi,

I want to convert query result into XML and pass that XML into another stored procedure as parameter.

Bellow is the stored procedure

Create PROC dbo.Select_Master_Table_Data
@Temp_Table XML
AS
SET NOCOUNT ON;

SELECT
Current$.Port#,
Current$.Protocol
,Current$.Process
,Current$.Services
FROM Current$
JOIN (
SELECT
Temp_Table.Row.query('./Protocol').value('.', 'varchar(100)') AS Protocol
,Temp_Table.Row.query('./Services').value('.', 'varchar(100)') AS Services
,Temp_Table.Row.query('./Process').value('.', 'varchar(100)') AS Process
FROM @Temp_Table.nodes('/Temp_Table/Row') AS Temp_Table(Row)
) AS Temp_Table ON
Temp_Table.Protocol = Current$.Protocol
AND Temp_Table.Services = Current$.Services
AND Temp_Table.Process = Current$.Process and Current$.IP='10.16.2.90';

RETURN @@ERROR;
GO

If i manually declared the XML as below its works correctly

DECLARE @Temp_Table XML;
SET @Temp_Table = '
<Temp_Table>
<Row>
<Protocol>TCP</Protocol>
<Services>seclogon</Services>
<Process>svchost.exe</Process>
</Row>
<Row>
<Protocol>UDP</Protocol>
<Services>'''' </Services>
<Process>discfcsn.exe</Process>
</Row>
<Row>
<Protocol>UDP</Protocol>
<Services>Netlogon</Services>
<Process>lsass.exe</Process>
</Row>
</Temp_Table>';
EXEC dbo.Select_Master_Table_Data @Temp_Table;

If i directly assign a query to the XML datatype i didn't find the result

DECLARE @Temp_Table XML;
SET @Temp_Table = 'select * from temp_port for XML RAW'
EXEC dbo.Select_Master_Table_Data @Temp_Table;

If a just query
select * from temp_port for XML RAW
i am getting below output in the XML file.
<row Protocol="TCP" Services="seclogon" Process="svchost.exe" />
<row Protocol="UDP" Process="discfcsn.exe" />
<row Protocol="UDP" Services="Netlogon" Process="lsass.exe" />

Can anyone please help me how to build a XML file from a query and pass that XML query into the Stored Procedure.

Thanks in advance.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-15 : 20:00:17

Add the options shown in red to your select query:


DECLARE @Temp_Table XML;
SET @Temp_Table = 'select * from temp_port for XML RAW, ELEMENTS, ROOT(''Temp_Table'')'
EXEC dbo.Select_Master_Table_Data @Temp_Table;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources