Intrebari

Re: Only functions and extended stored procedures can be executed from within a function.

rremus — Sat, 08 Sep 2007 17:19:29 GMT

SQL 2005 aduce imbunatatziri semnificative la manipularea XML si in sfirsit se poate renuntza la (oribilele si neperformantele) OPENXML si sp_xml_preparedocument. Incearca asa:

declare @x xml;

select @x = N'<boys>

</boys>';

select t.boy.value('@FirstName', 'varchar(20)') as FirstName,

t.boy.value('@Counter', 'int') as Counter,

t.boy.value('@Year', 'int') as Year

from @x.nodes('//boys/boy') t(boy)

Sau, pentru a obtine o performantza mai buna, declara schema datelor tale:

create xml schema collection [boys] as '<?xml version="1.0" encoding="utf-8"?>

<xs:schema

attributeFormDefault="unqualified"

elementFormDefault="qualified"

xmlns:xs="http://www.w3.org/2001/XMLSchema">

<xs:element name="boys">

<xs:complexType>

<xs:sequence>

<xs:element maxOccurs="unbounded" name="boy">

<xs:complexType>

<xs:attribute name="FirstName" type="xs:string" use="required" />

<xs:attribute name="Counter" type="xs:unsignedByte" use="required" />

<xs:attribute name="Year" type="xs:unsignedByte" use="required" />

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:schema>';

declare @x xml(boys);

select @x = N'<boys>

</boys>';

select t.boy.value('@FirstName', 'varchar(20)') as FirstName,

t.boy.value('@Counter', 'int') as Counter,

t.boy.value('@Year', 'int') as Year

from @x.nodes('//boys/boy') t(boy);

P.S pentru a obtine schema, pur si simplu incarca fragmentul XML in Visual Studio 2005 si selecteaza 'XML/Create Schema'...

Re: Only functions and extended stored procedures can be executed from within a function.

crestinul — Thu, 06 Sep 2007 15:14:31 GMT

multumesc pt raspunsuri

Re: Only functions and extended stored procedures can be executed from within a function.

B_gd_n[ ]Sahlean — Thu, 06 Sep 2007 12:40:41 GMT

Nu stiu daca te ajuta dar ai putea folosi o solutie de tipul:

INSERT INTO tabela | @variabila_tabela | #tabela_temporara

EXEC procedura_stocata_care_proceseaza_XML

SELECT *

FROM tabela | @variabila_tabela | #tabela_temporara

Re: Only functions and extended stored procedures can be executed from within a function.

crestinul — Thu, 06 Sep 2007 12:32:39 GMT

Naspa.Mie imi era la indemana asa pentru replicarea cu service broker,nu mi se pare o treaba ok asta sincer

Re: Only functions and extended stored procedures can be executed from within a function.

Diana — Thu, 06 Sep 2007 10:49:22 GMT

Bogdan, am citat artocolul de pe MSDN pentru ca "listeaza" niste limitari ale functiilor, si anume:

"# Functions can't execute stored procedures. A function can only execute functions and some xp's, but not sp's. This limitation immediately disqualifies function as a candidate for solving use cases 1 and 3.
# Functions can't create temporary or any tables. In order to return a result set of an xp, a function must be able to store the result set somewhere. Tables are a natural choice. Unfortunately, functions can't change the global database state. This includes the restriction of creating any temporary or permanent tables that will indeed change the global database state.
# Functions can't access temporary tables. Even if there were a pre-created global temporary table, SQL Server still disallows inserting data to this or any existing temporary table from within a function.
# Functions can't insert into an existing table. Another even more compromising idea is to create a permanent table outside of a function. However, inserting into an existing permanent table from within a function is considered changing global database state as well."

Ce-i drept, articolul nu este 100% "la obiect", iar lista de limitari o gasesti mai "la coada". Imi cer scuze... Trebuia sa precizez asta dintru inceput.

Re: Only functions and extended stored procedures can be executed from within a function.

B_gd_n[ ]Sahlean — Thu, 06 Sep 2007 10:34:16 GMT

Solutia de pe msdn2 nu ofera nici o solutie concreta pt. ca apelul unei proceduri stocate prin OPENQUERY se poate doar daca procedura nu are parametri sau daca pt. apelul procedurii folosesti constante pt. fiecare parametru al procedurii stocate. Ideea e ca nu poti apela OPENQUERY folosind variabile.

Re: Only functions and extended stored procedures can be executed from within a function.

Diana — Thu, 06 Sep 2007 10:22:47 GMT

Se pare ca functiile "user defined" nu pot "chema" proceduri. Vezi si

http://msdn2.microsoft.com/en-us/library/Aa175782(SQL.80).aspx

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=16969

Incearca sa folosesti in loc de functie o procedura.

Only functions and extended stored procedures can be executed from within a function.

crestinul — Thu, 06 Sep 2007 06:22:30 GMT

Am o functie multitabled in care fac un shreading la o variabila xml

Aceasta este functia:

ALTER FUNCTION [dbo].[boys]

(

-- Add the parameters for the function here

@boys xml

)

RETURNs @tbl_boys table(FirstName varchar(20),Counter int,Year int,id int)as

begin

Declare @idoc integer

exec sp_xml_preparedocument @idoc output,@boys

insert into @tbl_boys select * from openxml(@idoc,'/boys/boy') with (FirstName varchar(20),Counter int,Year int,id int)

exec sp_xml_removedocument @idoc

return

end

AM executat aceste instructiuni separat ca un batch ruleaza ok intprc setul de date dorit de mine.

Eu am nevoie insa de ele intr-o functie care sa intoarca acest set de date.

Cand fac

select * from boys(@xmlset) unde in xmlset am setul de date de tabularizat obtin eroarea din subject si nu-mi dau seama unde e buba

Multumesc