Problem bei der Erstellung einer UDF

[stefan2776]

Hallo,

ich habe folgendes Problem:

Ich möchte folgende Abfrage in eine UDF umwandeln, sodass ich die Variable @doc übergeben kann und den Select als Rückgabewert erhalte.

DECLARE @idoc int
DECLARE @doc varchar(2000)
SET @doc ='
<packmittel xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="verpackungsregel.xs d">
<packmitteldaten>
<ARTIKEL>FMatte100</ARTIKEL>
<KARTIKEL>FMatte100</KARTIKEL>
<KAPAZITAET>87</KAPAZITAET>
</packmitteldaten>
</packmittel>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/packmittel/packmitteldaten',3)
WITH (ARTIKEL varchar(10),
KARTIKEL varchar(20),
KAPAZITAET varchar(10))

Leider bekomme ich es nicht hin. Wenn ich folgedes versuche bekomme ich die Meldung:Meldung 137, Ebene 15, Status 2, Prozedur udf_readPackregel, Zeile 16Die "@idoc"-Skalarvariable muss deklariert werden.

USE [Ref]
GO
-- ================================================
-- Template generated from Template Explorer using:
-- Create Inline Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[udf_readPackregel]

(
-- Add the parameters for the function here
@doc as varchar(2000)

)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here

SELECT *
FROM OPENXML (@idoc,'/packmittel/packmitteldaten',3)
WITH (ARTIKEL varchar(10),
KARTIKEL varchar(20),
KAPAZITAET varchar(10))
)
AS
BEGIN
declare @idoc as int
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
END

Kann mir jemand helfen?

Vielen Dank!!!

Gruß Stefan

[O. Helper]

Hallo Stefan,

wenn Du Dir das mal lesbar formatierst, sieht man das Problem auch gleich.
[highlight=SQL]CREATE FUNCTION [dbo].[udf_readPackregel]
( -- Add the parameters for the function here
@doc as varchar(2000)
) RETURNS TABLE
AS
RETURN
(-- Add the SELECT statement with parameter references here
SELECT *
FROM OPENXML (@idoc,'/packmittel/packmitteldaten',3)
WITH (ARTIKEL varchar(10),
KARTIKEL varchar(20),
KAPAZITAET varchar(10))
)
AS
BEGIN
declare @idoc as int
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
END[/highlight]

1. Das @iDoc deklarierst Du unten im BEGIN/END, verwendest es aber schon oben im SELECT
2. Nach dem RETURN darf in einer UDF nichts mehr kommen, es stellt bereist das Ende da; die ganze Struktur stimmt also schon nicht.
Das wirst Du einmal komplett überarbeiten müssen, damit es funktioniert.

[hwoess]

Probiere es mal so:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[udf_readPackregel]

(
-- Add the parameters for the function here
@doc as varchar(2000)

)
RETURNS @docTbl TABLE
(
  ARTIKEL varchar(10),
  KARTIKEL varchar(20),
  KAPAZITAET varchar(10)
)
AS
BEGIN
declare @idoc as int
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

insert @docTbl SELECT *
FROM OPENXML (@idoc,'/packmittel/packmitteldaten',3)
WITH (ARTIKEL varchar(10),
KARTIKEL varchar(20),
KAPAZITAET varchar(10))

RETURN

END

bye,
Helmut

[stefan2776]

Hallo Helmut,

vielen Dank für Deine Lösung, so funktioniert es leider nur fast. Beim Aufruf der Funktion bekomme ich die Fehlermeldung "Meldung 557, Ebene 16, Status 2, Zeile 1
Nur Funktionen und erweiterte gespeicherte Prozeduren können innerhalb einer Funktion ausgeführt werden." Hast Du eine Idee wo der Haken ist?
(sp_xml_preparedocument ist eine erweiterte gespeicherte Prozedur und OPENXML ist eine Funktion!?)

Danke!

Viele Grüße!

Stefan

[hwoess]

Sorry, habe nicht getestet und nicht mitgedacht ...
"EXEC sp_xml_preparedocument" geht in einer UDF nicht
(sp_xml_preparedocument is eine Systemprozedur, keine erweiterte gespeicherte Prozedur - das wäre zB eine DLL)

bye,
Helmut

[stefan2776]

Gibt es eventuell einen Workaround?

Gruß Stefan

[hwoess]

Vielleicht sich mit XQUERY selber durch das XML durcharbeiten? Dann könnte man auf das sp_xml_preparedocument() verzichten.

bye,
Helmut

[stefan2776]

Hallo Helmut,

hättest Du da für mich einen Ansatz?

Das XML schaut so aus:

<packmitteldaten>
<ARTIKEL>Kis100</ARTIKEL>
<KARTIKEL>Kis100</KARTIKEL>
<KAPAZITAET>10</KAPAZITAET>
</packmitteldaten>

Gruß Stefan

[hwoess]

Habe jetzt eine Geburtstagsfeier am Programm - kann dir frühestens morgen abend weiterhelfen

bye,
Helmut

[stefan2776]

Danke Helmut, wäre super, wenn Du mir helfen könntest.
Viel Spaß bei Deiner Feier!

Gruß Stefan

[hwoess]

Hier mal ein kleines Beispiel dazu, hoffe, du kannst das als Basis für den Umbau deiner UDF verwenden

declare @doc xml
set @doc = '
<packmitteldaten>
  <ARTIKEL>Kis100</ARTIKEL>
  <KARTIKEL>Kis100</KARTIKEL>
  <KAPAZITAET>10</KAPAZITAET>
</packmitteldaten>
<packmitteldaten>
  <ARTIKEL>Kis200</ARTIKEL>
  <KAPAZITAET>20</KAPAZITAET>
</packmitteldaten>
<packmitteldaten>
  <ARTIKEL></ARTIKEL>
  <KARTIKEL>Kis300</KARTIKEL>
</packmitteldaten>
<packmitteldaten>
  <KAPAZITAET></KAPAZITAET>
</packmitteldaten>
'
declare @tempTbl table (
  ARTIKEL varchar(10),
  KARTIKEL varchar(20),
  KAPAZITAET int )

declare @x int, @anzahl int, @row xml

select @x = 1, @anzahl = @doc.value('data(count(/*))','int')
while @x <= @anzahl begin
  select @row = @doc.query('/packmitteldaten[position()=sql:variable("@x")]')
  insert into @tempTbl select
    @row.value('/packmitteldaten[1]/ARTIKEL[1]','varchar(10)'),
    @row.value('/packmitteldaten[1]/KARTIKEL[1]','varchar(20)'),
    @row.value('/packmitteldaten[1]/KAPAZITAET[1]','int')
  set @x = @x + 1
end
select * from @tempTbl

bye,
Helmut

PS: ACHTUNG - XML ist CASE SENSITIVE !!!

[stefan2776]

Hallo Helmut,

habe es mal so umgebaut, bekomme aber noch die Meldung
"Falsche Syntax in der Nähe von 'END'."

Kannst Du bitte noch mal einen Blick darauf werfen. Weiß gar nicht mehr wie ich Dir danken kann..... :-(

Gruß Stefan

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[udf_readPackregelNew]

(
-- Add the parameters for the function here
@doc as varchar(2000)

)

RETURNS @tempTbl TABLE
(
ARTIKEL varchar(10),
KARTIKEL varchar(20),
KAPAZITAET varchar(10)
)
AS
BEGIN
declare @x int, @anzahl int, @row xml

select @x = 1, @anzahl = @doc.value('data(count(/*))','int')
while @x <= @anzahl begin
select @row = @doc.query('packmittel/packmitteldaten[position()=sql:variable("@x")]')
insert into @tempTbl select
@row.value('/packmitteldaten[1]/ARTIKEL[1]','varchar(10)'),
@row.value('/packmitteldaten[1]/KARTIKEL[1]','varchar(20)'),
@row.value('/packmitteldaten[1]/KAPAZITAET[1]','int')
set @x = @x + 1
END

[hwoess]

Dein "END" ist das Schleifenende von ... while @x <= @anzahl begin ...
Du brauchst darunter noch ein "RETURN" und ein weiteres "END".
Außerdem musst du den Inputparameter von varchar() auf xml umstellen, sonst gehen die ganzen XQUERY-Statements nicht.

bye,
Helmut

[stefan2776]

Hallo Helmut,

jetzt funktioniert es endlich. Vielen, vielen Dank. Du weißt gar nicht wie Du mir geholfen hast.

Viele Grüße.

Stefan