[MSSQL/XML]Meerdere query's vanuit een SP export XML?

[Bunkerbuster]

voor MS SQL 2005

Ik wil graag meerdere query's in een stored procedure combineren die dan samen 1 xml blok vormen. Ik wil deze dan ook in 1 keer kunnen parsen met een xsl file op de server.

De losse query's werken maar dat leverd weer extra werk op omdat ik iedere keer weer naar de server moet om de xml op te halen. Daarnaast gebruik ik de query's enerzijds om een webpagina te vullen en anderzijds om een excel bestand te vullen.

Een union of union all werkt niet omdat de tabellen gelijk moeten zijn en dat zijn ze niet.

Kan het geen ik wil, of moet ik toch aparte query's schrijven?

SQL:

SELECT
    table1_auto as [id],
    table1_title as [title],
    table1_description as [description]
    FROM table1 as [table1]


SELECT
    table2_value as [value],
    table2_numbers as [numbers],
    FROM table2 as [table2]

FOR XML AUTO, ELEMENTS

gewenste output

XML:

<root>
 <table1>
   <id>1</id>
   <title>hello world</title>
   <description>lorem ipsum etc</description>
 <table1>
 <table1>
   <id>2</id>
   <title>hello you</title>
   <description>temp etc</description>
 <table1>
 <table2>
   <value>100</value>
   <numbers>25.00</numbers>
 <table2>
 <table2>
   <value>200</value>
   <numbers>50.00</numbers>
 <table2>
</root>

[RobIII]

Je zou een select into @variabele kunnen doen en die concatten?
Hmmm, wacht even, effe proberen voor ik roep
/edit:
Ja, toch dus. En dan in 2 variabelen mikken en die weer returnen met een root element er om heen ofzo.

Beetje ranzig wellicht, maar 't werkt:

SQL:

Declare @XML1 varchar(max)
Declare @XML2 varchar(max)

SET @XML1 = ( Select ... FOR XML AUTO,ELEMENTS )
SET @XML2 = ( Select ... FOR XML AUTO,ELEMENTS )

Select '<root>' + @XML1 + @XML2 + '</root>'

[ Voor 103% gewijzigd door RobIII op 21-09-2010 17:50 ]

[Bunkerbuster]

@Robill dank je wel, ik had iets gelijks maar dat werkte helemaal niet. De code die je hebt gegeven was nog niet helemaal compleet, maar was genoeg voor mij om het laatste stapje nog zelf er bij te zoeken http://www.sqlservercentr...663872-21-1.aspx#bm663984.

Als ik de varchar niet cast naar XML kreeg ik een foutmeldingen in de parser
"XML document must have a top level element sql", door het te casten naar xml, wordt dit opgelost.

Nogmaals erg bedankt en hieronder de volledige code

SQL:

USE [DATABASE]
GO
/****** Object:  StoredProcedure [dbo].[sqltoxml]    Script Date: 09/20/2010 12:51:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sqltoxml]
    -- Add the parameters for the stored procedure here

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    DECLARE @TEMP_XML1 varchar(max)
    DECLARE @TEMP_XML2 varchar(max)
    DECLARE @TEMP_XML_CONTAINER Varchar(max)    
    DECLARE @OUTPUT_XML as Xml
    

    SET @TEMP_XML1 = (SELECT 
        TABLE1_auto as [id],
        TABLE1_title as [titel]
    FROM TABLE1
    FOR XML AUTO, ELEMENTS)

    SET @TEMP_XML2 = (SELECT
        TABLE2_auto as [id],
        TABLE2_Time as [minuten],
        TABLE2_Description as [omschrijving]
    FROM TABLE2
    FOR XML AUTO, ELEMENTS)
    
    select @TEMP_XML_CONTAINER = @TEMP_XML1 + @TEMP_XML2
    select @OUTPUT_XML = cast(@TEMP_XML_CONTAINER as XML)
    select @OUTPUT_XML
END

[RobIII]

Bunkerbuster schreef op woensdag 22 september 2010 @ 11:21:
@Robill

* RobIII wijst naar z'n ondertitel

Tja, of het helemaal netjes is... nah. Ik bedacht me vannacht nog iets wat netter zou zijn maar dat zou ik even moeten experimenteren om te zien of dat kan/lukt.

Overigens lijkt me de container en output_xml een beetje overbodig (en wellicht zelfs 'duur' in gebruik; ik weet niet hoe slim SQL in dit geval is);

SQL:

    select @TEMP_XML_CONTAINER = @TEMP_XML1 + @TEMP_XML2
    select @OUTPUT_XML = cast(@TEMP_XML_CONTAINER as XML)
    select @OUTPUT_XML 

kan natuurlijk ook als:

SQL:

    select cast(@TEMP_XML1 + @TEMP_XML2 as XML) 

Maar wat wordt in zo'n geval je root element? Want (op 't oog) ziet dit er uit alsof het zoiets zou returnen:

XML:

<xml1>
...
</xml1>
<xml2>
...
</xml2>

terwijl je eigenlijk zoiets wil:

XML:

<root>
  <xml1>
  ...
  </xml1>
  <xml2>
  ...
  </xml2>
</root>

Ik heb nu even niet de juiste tools bij de hand om dit zelf te zien/experimenteren. En ik verwacht niet dat de + operator op 2 XML variabelen dan maar zelf een root verzint?

[ Voor 104% gewijzigd door RobIII op 22-09-2010 12:28 ]

[Bunkerbuster]

@Roblll

Misschien ben ik niet volledig geweest, maar hier ondervond ik ook geen probleem.

De stored procedure wordt aangeroepen vanuit een asp pagina en daar geef ik het blok zijn root element, of dit zo handig is weet ik niet, maar dit is wel de beste wijze zonder dat ik foutmeldingen krijg.

Onderstaande zit in een class gepropt, ik heb het dan ook even uit elkaar gehaald en achter elkaar gezet. Zoals je ziet "myCommand.Properties("xml root") = "root"" zorgt deze ervoor dat het valide xml wordt er wordt door enerzijds <?xml version="1.0" encoding="windows-1252"?> in de stream te plaatsen en de data tussen <root> elementen te zetten.

ASP:

conDBConnection = "Provider=SQLOLEDB;Driver={SQL Server};Server=SERVERNAAM;Database=DATABASENAAAM;UID=USERNAAM;PWD=PASSWORD"

Set myConnection = CreateObject("ADODB.Connection") 
myConnection.Open = conDBConnection
Set myCommand = CreateObject("ADODB.Command")   
myCommand.ActiveConnection = myConnection 

myCommand.CommandText = "STOREDPROCEDURE NAAM"

Set myStream = CreateObject("ADODB.Stream") 
myStream.Open 

myCommand.Properties("Output Stream") = myStream
myCommand.Properties("Output Encoding") = "windows-1252"
myCommand.Properties("xml root") = "root"

myCommand.Execute ,,1024 'instead of adExecuteStream 
myStream.Position=0 
myStream.Charset="windows-1252" 

Response.write myStream.ReadText