sql 2005 xml

8/13/2019 sql 2005 XML

1/22

2007 Microsoft Corporation. All rightsreserved.

Microsoft SQL 2000 Technical Articles

SQLXML Managed Classes

Scott Swigart3 Leaf Sol tions

March 2002

Applies to! Microsoft" SQL Server# 2000 Microsoft $is al St dio" .%&T

Summary: 'ow to se SQL(ML Managed Classes for .%&T code to retrieve (ML fro) a MicrosoftSQL Server data*ase or to generate (ML on the client side+ sing ad hoc , eries+ storedproced res+ annotated sche)as with (-ath+ or (ML te)plates. / printed pages1

Contents

ntrod ction SQL(ML Managed Classes *4ect Model 5sing SQL(ML Managed Classes Concl sion A*o t the A thor

Introduction

Co)panies have fo nd SQL(ML to *e a sef l technolog6 for a n )*er of scenarios. A co)pan6)a6 receive (ML doc )ents in vario s for)ats and wish to store this data in a Microsoft" SQL

Server# 2000 data*ase+ or a co)pan6 )a6 need to serve (ML doc )ents in vario s for)ats fortrading partners. n addition+ (ML doc )ents )a6 *e transfor)ed sing st6le sheets to targetvario s *rowsers+ handheld -Cs+ or other devices. or these scenarios+ SQL(ML allows 6o to send(ML data to and retrieve it fro) SQL Server. This capa*ilit6 )eans that the developer is notre, ired to a thor and )aintain all the code needed to transfor) (ML into so)e other for)at forcons )ption *6 the data*ase.

SQL(ML Managed Classes allow 6o to a thor .%&T code that ta8es advantage of the (ML feat resprovided *6 SQL(ML 3.0. 9o can write )anaged Microsoft $is al C:# or $is al ;asic" .%&T codethat tiliiff?ra)s. This white paper wille@plain the )echanics of sing SQL(ML Managed Classes for a variet6 of scenarios.

This paper is *ased on SQL(ML 3. 9o can download the latest release of SQL(ML http!BB)sdn.)icrosoft.co)Bs,l@)lB .

SQLXML Managed Classes Object Model

Three pri)ar6 classes are sed to access the (ML f nctionalit6 of SQL Server. These are!

SqlXmlCommand . 5sed to send a TransactDSQL state)ent to the data*ase+ e@ec te a

stored proced re+ or , er6 the data*ase sing other technologies s ch as annotated
http://msdn2.microsoft.com/en-us/library/aa902660(printer).aspx#sqlxml_intromanagedclasses_topic1%23sqlxml_intromanagedclasses_topic1http://msdn2.microsoft.com/en-us/library/aa902660(printer).aspx#sqlxml_intromanagedclasses_topic1%23sqlxml_intromanagedclasses_topic1http://msdn2.microsoft.com/en-us/library/aa902660(printer).aspx#sqlxml_intromanagedclasses_topic2%23sqlxml_intromanagedclasses_topic2http://msdn2.microsoft.com/en-us/library/aa902660(printer).aspx#sqlxml_intromanagedclasses_topic3%23sqlxml_intromanagedclasses_topic3http://msdn2.microsoft.com/en-us/library/aa902660(printer).aspx#sqlxml_intromanagedclasses_topic4%23sqlxml_intromanagedclasses_topic4http://msdn2.microsoft.com/en-us/library/aa902660(printer).aspx#sqlxml_intromanagedclasses_topic5%23sqlxml_intromanagedclasses_topic5http://msdn.microsoft.com/sqlxml/http://msdn.microsoft.com/sqlxml/http://msdn2.microsoft.com/en-us/library/aa902660(printer).aspx#sqlxml_intromanagedclasses_topic1%23sqlxml_intromanagedclasses_topic1http://msdn2.microsoft.com/en-us/library/aa902660(printer).aspx#sqlxml_intromanagedclasses_topic2%23sqlxml_intromanagedclasses_topic2http://msdn2.microsoft.com/en-us/library/aa902660(printer).aspx#sqlxml_intromanagedclasses_topic3%23sqlxml_intromanagedclasses_topic3http://msdn2.microsoft.com/en-us/library/aa902660(printer).aspx#sqlxml_intromanagedclasses_topic4%23sqlxml_intromanagedclasses_topic4http://msdn2.microsoft.com/en-us/library/aa902660(printer).aspx#sqlxml_intromanagedclasses_topic5%23sqlxml_intromanagedclasses_topic5http://msdn.microsoft.com/sqlxml/

8/13/2019 sql 2005 XML

2/22

8/13/2019 sql 2005 XML

3/22

8/13/2019 sql 2005 XML

4/22

SW.Write(sr.ReadToEnd()); reads the (ML inp t strea) and sends the data thro ghCryptoStream and FileStream to store the encr6pted (ML on dis8.

SqlXmlCommand$%&ecute#oStream

=ather than ret rn the (ML data fro) SQL Server as a si)ple Stream o*4ect+ 6o )a6 wish to send

the (ML res lt set directl6 to the destination witho t )odification. n this case+ 6o can se the%&ecute#oStream )ethod to send the res lting (ML directl6 to a 'ileStream o*4ect+ a(et)or*Stream o*4ect+ or+ in the case of AS-.%&T+ the "esponse o*4ect. The following codesends the (ML res lts directl6 to a 'ileStream o*4ect. The Process class is then sed to displa6the res lts in nternet &@plorer ass )ing that (ML files are associated with nternet &@plorer+which is the defa lt config ration1.

SqlXmlComman cm ! new SqlXmlComman (Northwin ConnString);

cm .,oot-ag!"pro ucts";

cm .Comman -ype ! SqlXmlComman -ype.Sql;

cm .Comman -e t! "S&%&C- =,OM pro ucts =O, XM% AE-O";

F#$eStream % = ne F#$eStream!&"c'(products.xm$")F#$eMode.Create ;

cmd.ExecuteToStream!% ;

%.C$ose! ;

S stem.+#a,nost#cs.-rocess.Start!&"c'(products.xm$" ;

This code retrieves all the rows and col )ns fro) the products ta*le and displa6s the res lts asfollows!

FG ml #ersion!"2.5" enco ing!"utfH6" GFOr ers

FOr er &mployeeID!"4" CustomerID!"JIN&-" Or erID!"25876"

FOr erDate 2 KH59H57-55?55?55FLOr erDate

F,equire Date 2 KH56H52-55?55?55FL,equire Date

FShippe Date 2 KH59H2K-55?55?55FLShippe Date

FShipJia

8/13/2019 sql 2005 XML

5/22

FLOr er

FOr er &mployeeID!"K" CustomerID!"-OMSP" Or erID!"2587 "


F,equire Date 2 KH56H2K-55?55?55FL,equire Date FShippe Date 2 KH59H25-55?55?55FLShippe Date

FShipJia 2FLShipJia

F=reight 22.K2FL=reight

FShipName -oms Spe ialit tenFLShipName

FShipA ress %uisenstr. 76FLShipA ress

FShipCity M nsterFLShipCity

FShipPostalCo e 77569FLShipPostalCo e FShipCountry ermanyFLShipCountry

FLOr er ...

FLOr ers

SqlXmlCommand$%&ecuteXml"eader

T6picall6 6o retrieve (ML res lts *eca se 6o want to wor8 data in an (ML for)at. 9o )ightretrieve data as (ML so that 6o can render o tp t for ) ltiple devicesG 6o )ight want to send(ML data to trading partners in vario s for)atsG or 6o )ight *e e)itting (ML so that it can *econs )ed *6 an (MLDaware application. or these 8inds of scenarios+ 6o sho ld se the

%&ecuteXml"eader )ethod+ which ret rns an Xml"eader o*4ect. This is a highDperfor)anceo*4ect that lets 6o iterate thro gh the nodes in the (ML res lt set. f 6o need rando) access tothe (ML res lts+ 6o can pass Xml"eader as an arg )ent to the constr ctor of an Xml ocument o*4ect. The Xml ocument o*4ect gives 6o f ll >oc )ent *4ect Model > M1 Level / and Level 2Core s pport for )anip lating the res lt set.

The following sa)ple retrieves the res lts as an Xml"eader o*4ect and passes it to the constr ctorof an Xml ocument o*4ect. nce the data is in an Xml ocument o*4ect+ ) ltiple (-ath , eriescan e@tract res lts witho t going *ac8 to the data*ase. n this case+ prod cts with a nit pricegreater than 20 are e@tracted.


cm .,oot-ag!"pro ucts";cm .Comman -ype ! SqlXmlComman -ype.Sql;

cm .Comman -e t! "S&%&C- =,OM pro ucts =O, XM% AE-O";

Xm$Reader xr = cmd.ExecuteXm$Reader! ;

Xm$+ocument xd = ne Xm$+ocument! ;

8/13/2019 sql 2005 XML

6/22

xd.Load!xr ;

Xm$ odeL#st xn$ = xd.Se$ect odes!"//products0&Un#t-r#ce 1 234" ;

%oreac5!Xm$ ode xn #n xn$

6

Conso$e.7r#teL#ne!xn.OuterXm$ ;

8

5p to this point+ all the e@a)ples have sed ad hoc , eries to retrieve (ML fro) SQL Server. 9ocan also e@ec te stored proced res that ret rn (ML. Consider the following stored proced re!

C,&A-& P,OC&DE,& *o. et&mployeesXml

AS

S&%&C- =irstName3 %astName =,OM employees =O, XM% AE-O ,&-E,N

n this case+ the = (ML A5T cla se is part of the S&L&CT state)ent inside the proced re *od6.This state)ent can *e e@ec ted as follows!


cm .,oot-ag ! "&mployees";

cm .Comman -e t ! "EXEC 9etEmp$o eesXML";

Xml,ea er r ! cm .& ecuteXml,ea er();

SqlXmlCommand$ClientSideXml

n )an6 cases+ 6o cannot edit e@isting stored proced res to ret rn an (ML res lt set. or e@a)ple+consider the following stored proced re!

A%-&, P,OC&DE,& *o. et&mployees

AS

S&%&C- =irstName3 %astName =,OM employees

,&-E,N

This is a t6pical stored proced re that does not ret rn (ML. n addition+ there are )an6circ )stances where+ for perfor)ance or other reasons+ 6o want to have the (ML generated onthe client rather than directl6 fro) the data*ase. or these scenarios+ 6o can se theClientSideXml propert6 of the SqlXmlCommand o*4ect+ as shown *elow!


8/13/2019 sql 2005 XML

7/22

cm .,oot-ag ! "&mployees";

cmd.C$#entS#deXm$ = true;

cm .Comman -e t ! "EXEC 9etEmp$o ees FOR XML ESTE+";

Xml,ea er r ! cm .& ecuteXml,ea er();XmlDocument ! new XmlDocument();

.%oa ( r);

Console.Brite%ine( .OuterXml);

To generate (ML on the client side involves two i)portant steps!

9o ) st set the ClientSideXml propert6 of the SqlXmlCommand o*4ect to true .

The Command#e&t propert6 ) st contain either F = (ML %&ST&>F or F = (ML =AEF or

F = (ML &@plicitF. This cla se is intercepted *6 the L& >; provider+ and indicates that theres lt set sho ld *e converted to (ML.

&ven tho gh the stored proced re has not *een )odified+ 6o end p with an Xml"eader o*4ect onthe client.

This )echanis) can also *e sed for ad hoc , eries as shown *elow!

cmd.C$#entS#deXm$ = true;

cm .Comman -e t ! "SELECT * FROM products FOR XML ESTE+";

Xml,ea er r ! cm .& ecuteXml,ea er();

Annotated sc+emas

f 6o are sing ad hoc , eries or stored proced res to , er6 SQL Server 2000+ the col )ns in theres lt set are seriali+ A5T or =AE (ML )odes+ 6o can do Select Hfro) -rod cts = (ML %&ST&>+ &L&M&%TS.

f 6o want to change the for)at of the ret rned (ML+ 6o can se an annotated sche)a to definewhich col )ns will *e e@pressed as ele)ents+ and which col )ns will *e e@pressed as attri* tes.Annotated sche)as let 6o f rther )odif6 the res lt set sing standard (-ath. An annotatedsche)a is an (ML sche)a doc )ent that specifies *oth the ta*les and col )ns that 6o wish to

, er6+ and the str ct re of the res lting (ML. SQL(ML 2.0 s pported two versions of the sche)as+(ML >ata =ed ced (>=1 and E3 (ML Sche)a >efinition (S>1. This sche)a s6nta@ predated theEorld Eide Ee* Consorti ) E31 release of a sche)a reco))endation. SQL(ML 3.0 f ll6 s pportsthe c rrent E3 (ML Sche)a >efinition (S>1.

Consider the following sche)a!

FG ml #ersion!"2.5" enco ing!"utfH6" G

8/13/2019 sql 2005 XML

8/22

F s?schema mlns? s!"http?LLwww.we* g or *inI=elease director61. Therefore+ the path incl des F I IF tolocate the sche)a relative to the e@ec ta*le. 9o can also se an a*sol te path+ or place the

8/13/2019 sql 2005 XML

9/22

sche)a in the sa)e ph6sical director6 as the e@ec ta*le+ in which case 6o wo ld 4 st give the filena)e+ rders/.@sd.

The sche)a file is sed to )ap the res lting (ML to ta*les and col )ns in the data*ase. TheCommand#e&t propert6 specifies an (-ath , er6 that defines the (ML res lt set. n other words+the sche)a )a6 specif6 that all orders *e ret rned+ * t Command#e&t )a6 li)it the res lt toorders that )atch a specific criterion. n the c rrent e@a)ple+ the following (ML is ret rned!

FG ml #ersion!"2.5" enco ing!"utfH6" G

FOr ers

FOr er &mployeeID!"4" CustomerID!"JIN&-" Or erID!"25876"


FShipName Jins et alcools Che#alierFLShipName

FLOr er

FOr er &mployeeID!"K" CustomerID!"-OMSP" Or erID!"2587 "


FShipName -oms Spe ialit tenFLShipName

FLOr er

...

FLOr ers

Also notice that the annotated sche)a )a8es it eas6 to ret rn so)e col )ns as ele)ents and othercol )ns as attri* tes.

!sing ,isual Studio $(%# to generate annotated sc+emas

#o use ,isual Studio $(%# to simpli-y t+e process o- generating annotated sc+emas. -ollo)t+ese steps:

/. pen an e@isting $is al St dio .%&T pro4ect+ and on the Project )en + clic8 Add (e)

Item .

2. n the Add (e) Item dialog+ in the te)plates pane+ select XML Sc+ema + and clic8 Open .

3. n the ,ie) )en + clic8 Ser/er %&plorer or press CT=LJALTJS1 to open Server

&@plorer.

K. &@pand Ser/ers + + SQL Ser/ers + + (ort+)ind. and #ables .

. >rag and drop the Products ta*le on to the design s rface.

. At the lowerDleft corner of the sche)a designer+ clic8 the XML ta*. 9o sho ld see the

following code!

9. FG ml #ersion!"2.5" enco ing!"utfH6" G

8/13/2019 sql 2005 XML

10/22

6. F s?schema i !"XM%Schema2"

. targetNamespace!"http?LLtempuri.orgLXM%Schema2. s "

25. element=ormDefault!"qualifie "

22. mlns!"http?LLtempuri.orgLXM%Schema2. s "

28. mlns?mstns!"http?LLtempuri.orgLXM%Schema2. s "

2

8/13/2019 sql 2005 XML

11/22

8/13/2019 sql 2005 XML

12/22

8/13/2019 sql 2005 XML

13/22

8/13/2019 sql 2005 XML

14/22

Or erT2TShippe DateTelement/3R$2.R-'QBC8 AS

Or erT2TShipJiaTelement/3R$2.R-''

8/13/2019 sql 2005 XML

15/22

Fsql?query

S&%&C- Pro ucts.Pro uctID3 Pro ucts.Pro uctName3

Suppliers.CompanyName AS SupplierName3 Categories.CategoryName3

Pro ucts.$uantityPerEnit3 Pro ucts.EnitPrice3 Pro ucts.EnitsInStoc03 Pro ucts.EnitsOnOr er3 Pro ucts.,eor er%e#el3

Pro ucts.Discontinue

=,OM Pro ucts

INN&, OIN

Suppliers ON Pro ucts.SupplierID ! Suppliers.SupplierID

INN&, OIN

Categories ON Pro ucts.CategoryID ! Categories.CategoryID =O, XM% ,AB

FLsql?query

FLPro ucts

9o can then se the following section of code to e@ec te this te)plate and displa6 the res lts!


cm .,oot-ag ! "Pro ucts";

cmd.CommandText = &"..(..(-roducts.xm$";

cmd.CommandT pe = S:$Xm$CommandT pe.Temp$ateF#$e;

=ileStream f ! new =ileStream(>"c?@pro ucts. ml"3=ileMo e.Create);

cm .& ecute-oStream(f);

f.Close();

System.Diagnostics.Process.Start(>"c?@pro ucts. ml");

Aut+oring templates using ,isual Studio $(%# Query 0uilder

Altho gh $is al St dio .%&T doesnNt directl6 s pport a thoring (ML te)plates+ 6o can get )ost ofthe wor8 done sing Q er6 ; ilder+ as o tlined in the following steps!

/. Eith a pro4ect open in $is al St dio .%&T+ on the Project )en + clic8 Add (e) Item or

press CT=LJS' TJA1.

2. n the Add (e) Item dialog+ in the te)plates pane+ select XML 'ile .

3. n the (ame field+ enter orders$&ml + and clic8 Open .

8/13/2019 sql 2005 XML

16/22

8/13/2019 sql 2005 XML

17/22

8/13/2019 sql 2005 XML

18/22

49. System.Diagnostics.Process.Start(>"c?@or ers. ml");

SqlXmlParameter

5p to this point+ all the , eries+ stored proced res+ sche)as+ (-ath e@pressions+ and te)plates

have *een static. ften+ however+ 6o want to )odif6 the , er6 *ased on so)e criteria. ore@a)ple+ 6o 8now that 6o want to select orders *6 c sto)er+ * t it isnNt ntil r n ti)e that 6o8now the specific c sto)ers whose orders 6o want to see. To )a8e these , eries d6na)ic+ 6ocan para)eteri

8/13/2019 sql 2005 XML

19/22

8/13/2019 sql 2005 XML

20/22

s:$'5eader 1

s:$'param name=CategoryID =O, XM% AE-O

FLsql?query

FL,OO-

The val e for Category!" can then *e specified sing the SqlXmlParameter o*4ect+ as follows!


cm .,oot-ag ! "Or ers";

cm .Comman -e t ! >"..@..@pro ucts8. ml";

cm .Comman -ype ! SqlXmlComman -ype.-emplate=ile;

S:$Xm$-arameter p = cmd.Create-arameter! ;

p. ame = "&Cate,or D+";

p.Ba$ue = " ";

=ileStream f ! new =ileStream(>"c?@or ers. ml"3=ileMo e.Create);

cm .& ecute-oStream(f);

f.Close();

System.Diagnostics.Process.Start(>"c?@or ers. ml");

SqlXmlAdapter

The SqlXmlAdapter o*4ect can *e sed to pdate the data*ase with an (ML >iff?ra). This issef l if 6o have generated (ML data sing an annotated sche)a+ the ser has )odified the data+

and 6o wish to store the changes *ac8 in the data*ase. >iff?ra)s s pport insert+ pdate+ anddelete operations. The SqlXmlAdapter o*4ect contains two )ethods+ which are descri*ed in thefollowing ta*le.

Met+od escription

'ill2 DataSet ds 3 -op lates a ataSet o*4ect with (ML dataretrieved fro) SQL Server.

!pdate2 DataSet ds 3 5pdates SQL Server to reflect changes to theataSet o*4ect sing an (ML >iff?ra).

or e@a)ple+ ass )e that 6o have created the following sche)a!

8/13/2019 sql 2005 XML

21/22

8/13/2019 sql 2005 XML

22/22

About t+e Aut+or

Scott Swigart is a senior principal of 3 Leaf Sol tions+ a co)pan6 that speciali

sql 2005 xml

Documents