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

SQL Server Administration (2005)

xml Data

Author

Topic

sqldba20
Posting Yak Master

183 Posts

Posted - 2009-02-04 : 15:35:00

Folks:

We are pulling xml data from one server to another server database using OPENQUERY and it was working fine until we noticed that the xml data has increased now and it is no longer pulling the data. We have defined 'nvarchar(max)'. Is there anything else we can use or a workaround so that the xml data can be pulled?

DECLARE @SqlStr NVARCHAR(MAX)

CREATE TABLE #TempXMLTable (Id INT IDENTITY(1,1) PRIMARY KEY, TempXML XML)

DECLARE @AnalJobId VARCHAR(50)
DECLARE @AnalLbl VARCHAR(50)
DECLARE @AnalPost VARCHAR(50)
SET @AnalLbl = 'MDSupp'

SET @AnalJobId ='607933'
SET @SqlStr =
'SELECT * FROM OPENQUERY (SERVERB,''
SELECT (SELECT (
SELECT
AnalJobId AS AnalJobId,
AnalyJobOrd AS AnalJobOrd,
UpdateDate AS UpdateDate,
MetaData,
[Entry]
FROM SERVERA.dbo.tblAnalTest
WHERE MetaData.exist (''''//AnalPers[EntryDirection="Output" or IsAnalResult="true"]'''') = 1
AND AnalJobId = '+@AnalJobId+' AND AnalJobOrd <> 0
FOR XML PATH (''''RESXML''''),type)
FOR XML PATH (''''Results''''))'')'

--SELECT @SqlString

INSERT #TestXMLTable
EXEC(@SqlStr)

SELECT * FROM #TestXMLTable

DROP table #TestXMLTable

Thanks !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-04 : 15:44:58

On which server are you executing the query?

The query is run at ServerB, but then ServerB queries ServerA...

E 12°55'05.63"
N 56°04'39.26"

sqldba20
Posting Yak Master

183 Posts

Posted - 2009-02-04 : 15:52:59

My bad.... The query is run on SERVERA getting xml data from SERVERB. It was a typo in the script it should have been the database name instead of server name. Here is the updated script:

DECLARE @SqlStr NVARCHAR(MAX)

CREATE TABLE #TempXMLTable (Id INT IDENTITY(1,1) PRIMARY KEY, TempXML XML)

DECLARE @AnalJobId VARCHAR(50)
DECLARE @AnalLbl VARCHAR(50)
DECLARE @AnalPost VARCHAR(50)
SET @AnalLbl = 'MDSupp'

SET @AnalJobId ='607933'
SET @SqlStr =
'SELECT * FROM OPENQUERY (SERVERB,''
SELECT (SELECT (
SELECT
AnalJobId AS AnalJobId,
AnalyJobOrd AS AnalJobOrd,
UpdateDate AS UpdateDate,
MetaData,
[Entry]
FROM databasename.dbo.tblAnalTest
WHERE MetaData.exist (''''//AnalPers[EntryDirection="Output" or IsAnalResult="true"]'''') = 1
AND AnalJobId = '+@AnalJobId+' AND AnalJobOrd <> 0
FOR XML PATH (''''RESXML''''),type)
FOR XML PATH (''''Results''''))'')'

--SELECT @SqlString

INSERT #TestXMLTable
EXEC(@SqlStr)

SELECT * FROM #TestXMLTable

DROP table #TestXMLTable

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-04 : 16:00:59

So "databasename" is a third database? Neither ServerA (on which the code is running) not ServerB (where the code is executing)?

E 12°55'05.63"
N 56°04'39.26"

sqldba20
Posting Yak Master

183 Posts

Posted - 2009-02-04 : 16:19:28

No database name is not a third database name.......

The query/script in red runs on SERVERA (Management Studio) and pulls xml data from SERVERB from table named 'databasename.dbo.tblAnalTest' and inserts into temp table.

Thanks !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-04 : 16:26:09

Why use dynamic SQL at all?

DECLARE @AnalJobId VARCHAR(50)
SET	@AnalJobId = '607933'

--INSERT	#TestXMLTable
--	(
--		TempXML
--	)
SELECT	AnalJobId AS AnalJobId, 
	AnalyJobOrd AS AnalJobOrd, 
	UpdateDate AS UpdateDate,
	MetaData, 
	[Entry]
FROM	ServerB.DatabaseName.dbo.tblAnalTest
WHERE	MetaData.exist('//AnalPers[EntryDirection="Output" or IsAnalResult="true"]') = 1
	AND AnalJobId = @AnalJobId
	AND AnalJobOrd <> 0
FOR XML	PATH('RESXML'),
	ROOT('Results'),
	TYPE

SELECT	*
FROM	#TestXMLTable

DROP TABLE #TestXMLTable

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources