The server now seems to be very popular with XML, and all sorts of things are starting to support XML. The mobile suit that is good at doing things naturally is to take the lead in everything. Browsers, Office, SQL, MDAC, and
XML mixed with one ha.
Last year, MS in MDAC2.0 or 2.1 provides a function to save a result set as XML, in SQL 2000, the use of XML more convenient, SQL query can
Returns an XML directly, and can also modify the data in the database through XML.
To return XML is very simple, just add a FOR XML auto after your SQL statement.
Example:
SELECT top 1 IDs, Filename, Download_time, introduce from Download for XML AUTO
<download id= "1" "Filename=" Ayako_katagiri.exe "download_time=" 1998-12-21t00:00:00 "Introduce=" Piece Tong Cai Zi
Winamp Skin "/>
If you use for XML raw, you will return
<row id= "1" filename= "Ayako_katagiri.exe" download_time= "1998-12-21t00:00:00" introduce= "Color Zi winamp
Skin "/>
If you use the for XML AUTO, ELEMENTS, the result is
<download>
<ID>1</ID>
<Filename>ayako_katagiri.exe</Filename>
<Download_Time>1998-12-21T00:00:00</Download_Time>
<Introduce> Tong Color zi Winamp skin</introduce>
</download>
Using for XML explicit a lot of trouble, before you define a bunch of things, but it's also powerful,
I will not introduce the details, interested can see online book:using EXPLICIT Mode
SELECT top 1 1 as Tag, Null as parent,id as [Downloadxml!1!id], Filename as
[downloadxml!1! Filename] from download for XML EXPLICIT
<downloadxml id= "1" filename= "Ayako_katagiri.exe"/>
The above is how to return XML data, let's take a look at how to use XML as a table
OPENXML can turn XML data into a table
Example:
DECLARE @l int
DECLARE @txtXML varchar (1000)
Set @txtXML = '
<Root>
<download id= "1" filename= "Ayako_katagiri.exe"/>
<download id= "2" filename= "Beauti.mid"/>
<download id= "3" filename= "Bee.mp3"/>
<download id= "4" filename= "Behavior.js"/>
<download id= "5" filename= "Cactivex.zip"/>
<download id= "6" filename= "Chatsrvr.exe"/>
<download id= "7" filename= "Chatter.exe"/>
<download id= "8" filename= "China.zip"/>
<download id= "9" filename= "CIYUN.GB"/>
<download id= "Ten" filename= "CI_SHANXI1.GB"/>
</Root>
'
EXEC sp_xml_preparedocument @l OUTPUT, @txtXML
SELECT * from OPENXML (@l, '/root/download ', 1) with (ID Int,filename
varchar (50))
EXEC sp_xml_removedocument @l
Execution results
ID Filename
----------- --------------------------------------------------
1 Ayako_katagiri.exe
2 Beauti.mid
3 Bee.mp3
4 Behavior.js
5 Cactivex.zip
6 Chatsrvr.exe
7 Chatter.exe
8 China.zip
9 CIYUN.GB
Ten CI_SHANXI1.GB
(a row (s) affected)
These features mean that we can use SQL 2000来 to generate an XML and get the return value of the client via XML (not necessarily through tools such as ASP.
Access to the XML returned by SQL 2000 through a URL, but I'm not quite sure about this feature, so I'm not going to introduce it.
Very cool Function! But it's like pretending to be cool. 8-)
