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

---

Secolul XXI ori va fi religios ori nu va fi deloc

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.

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.

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.

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

---

Secolul XXI ori va fi religios ori nu va fi deloc

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

multumesc pt raspunsuri

---

Secolul XXI ori va fi religios ori nu va fi deloc

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>

  <boy FirstName="kuku" Counter="8" Year="7" />

  <boy FirstName="dfdfdf" Counter="0" Year="88" />

</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>';

go

 

declare @x xml(boys);

select @x = N'<boys>

  <boy FirstName="kuku" Counter="8" Year="7" />

  <boy FirstName="dfdfdf" Counter="0" Year="88" />

</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'...

---

http://rusanu.com