Training
Certifications
Books
Special Offers
Community




 
Programming Microsoft® SQL Server™ 2000 with XML
Author Graeme Malcolm of Content Master Ltd.
Pages 304
Disk 1 Companion CD(s)
Level All Levels
Published 05/30/2001
ISBN 9780735613690
ISBN-10 0-7356-1369-9
Price(USD) $59.99
To see this book's discounted price, select a reseller below.
 

More Information

About the Book
Table of Contents
Sample Chapter
Index
Related Series
Related Books
About the Author

Support: Book & CD

Rate this book
Barnes Noble Amazon Quantum Books

 

Index


Note: Page numbers in italics refer to figures or tables.

Numbers and Symbols
& (ampersand), 64–65, 76
@ (at sign), 239
# (bookmark indicator), 65
$ (dollar sign), 100, 144–45, 149
% (percentage symbol), 65
+ (plus sign), 65
? (question mark), 64–65
/ (slash), 65, 239
.. (double period), 240

A
ActiveConnection property, 39
Active Server Pages. See ASP (Microsoft Active Server Pages)
ActiveX Data Objects. See ADO (Microsoft ActiveX Data Objects)
adding data. See inserting XML data
adding items to shopping basket, 188–90
AddVirtualDirectory method, 62
adExecuteStream option, 41–42
ADO (Microsoft ActiveX Data Objects), 35–47
   Base Path property, 45–46
   Command object, 36–37, 39–45, 95–96
   CommandText property, 40–42
   Connection object, 36, 39
   executing XML queries with, 39–45
   mapping schemas with, 95–99
   Output Encoding property, 47
   Record object, 36
   Recordset object, 36
   Response object, 47
   retail Web site sample, 174
   SS Stream Flags property, 46
   Stream object, 36, 40–45, 47, 95–96
   style sheets, applying, 45
   templates, 36–39
   XSL property, 45
ADO.NET, 35
after elements, 141, 146–47, 149, 151
aggregation
   AS keyword, 21
   AUTO mode, 38
   BizTalk functoids, 222
   edge tables for, 136
aliasing
   columns, 16–17, 20–23
   elements, 17
align attribute, HTML, 229
AllowFlags property, 72
Allow POST option, 79
Allow Template Queries option, 72
Allow URL Queries option, 79
Allow XPath option, 99–100
&amp, 76
ampersand (&), 64–65, 76
annotated schemas, 58
annotations
   advanced, 102
   datatype, 116–17
   ID, 110–13
   id-prefix, 110–13
   IDREF, 110–13
   IDREFS, 110–13
   is-constant, 104–5
   key-fields, 106–9, 116
   key field specification, 106–9
   limit-field, 109–10
   limit-value, 109–10
   map-field, 105–6
   to mapping schemas, 90–93
   overflow-field, 117
   relationship element, 92–93, 106–9
   SQL data type support, 140
   target-namespace, 113–14
   url-encode, 116–17
   use-cdata, 117
apply-templates command, 248–50
architecture
   messaging, 223–34
   retail Web site, sample, 174
AS keyword, 21
ASP (Microsoft Active Server Pages)
   COM with, 225
   Response object, 47
   retail Web site sample, 174–75
   Session object, 186
   XML style sheets, 45
at-identity attribute, 150
attribute-centric mapping, 5–6, 15
   AUTO mode, 19
   flag parameter, 122, 126
attribute command, 246–48
attribute keyword, 239, 253–54
AttributeName parameter, 20–21
attributes, 5–6
   column aliases, 16
   ID data types, 8–9
   names with xmltest directive, 30–31
   searching for, parameter, 122, 126
   style sheet, 246–48
   XML, tags, 230–31
   XPath expressions of, 238
AttributeType keyword, 253–54
authentication
   IIS virtual directories, 53–55
   retail Web site, sample, 185–88
   SQL Server security, 54–55
   virtual directories, 62
AUTO mode, 14, 16–19
   aggregate values, 38
   aliases, 17
   column aliases, 17
   default virtual name, 59
   ELEMENTS option, 18–19
   GROUP BY, 19
   joins, 17–18

B
B2B (business to business), 10. See also delivery .company e-commerce example
B2C (business to consumer), 9–10. See also retail Web site, sample
B2E (business to enterprise), 10
Base Path property, 45–46, 96
before elements, 141–42, 146–47, 149
BEGIN TRAN, 132
BeginTrans method, 132
bide directive, 22
BINARY BASE64, 14, 33–34
binary data
   mapping schemas, retrieving with, 116–17
   retrieval, 33–34
   virtual names for, 59
   XPath references to, 34
bin.base64 XML data type, 116–17
BizTalk Server 2000, 216–26
   Application Integration Components (AICs), 225
   channels, 223–25
   defining business documents, 216–21
   distribution lists, 223
   documents, 223–25
   dynamic routing, 225
   Editor, 217
   envelopes, 223
   functoids, 222
   Home Organization, 223
   IInterchange interface, 224
   Mapper, 221–23
   messaging, 216–24
   Messaging Manager, 223–24
   organizations, 223
   ports, 223–25
   receive function, 224
   receiving documents, 225–26
   repository, 221–22
blind updates, 154
bookmark indicator (#), 65
bulk load component. See XML bulk load component
business document exchange, 10
business to business. See B2B (business to business)
business to consumer. See B2C (business to consumer)
business to enterprise. See B2E (business to enterprise)

C
cascading deletes, 153
case sensitivity, 232–34
catalog update example, 212–15
CDATA
   retrieving with cdata directive, 31
   retrieving with schemas, 117
cdata directive, 22, 31
character data
   nonparsed, 31
   XML, treating as text, 140
characters, illegal in updategram, 142
CheckConstraints property, 160–61
checking out shopping basket, 195–200
child nodes, 238–40
classes, 2
client-side XSL processing, 78–79
closed content models, 88
closed models, 255
closing tags, 232–34
column aliases, 16–17, 20–23
column patterns, 127–28
columns
   aliases, 16–17, 20–23
   AUTO mode, 16–19
   binary, mapping to, 116–17
   criteria, 160–64
   directives, aliases, 20–23
   element directive, 22–23
   ELEMENTS option, 18–19
   elements with no corresponding, 102–5
   EXPLICIT mode, 19
   filtering by value, 109–10
   hiding results of, 28–29
   intrinsic mapping to, 142
   mapping, 91–93, 221–23
   metadata, 19–21
   names in universal tables, 20
   overflow (see overflow columns)
   RAW mode, returned in, 15–16
   returning as subelements, 14
   timestamp, 155
   WITH clause, OpenXML function, 123
   XML bulk load component, creating with, 166–68
COM+ components, 81–82
Command object, 36, 39–45, 95–96
   ActiveConnection property, 39
   Base Path property, 45–46
   CommandStream property, 43–45
   Dialect property, 40, 97
   Mapping Schema property, 96
   OutputStream property, 40–42
   sql:query tag, 37
   SS Stream Flags property, 46
   XML bulk load component, 160
   Xml Root property, 96
   XPath expressions with, 97
   Xsl property, 45, 98
CommandStream property, 43–45
CommandText property, 40–42
comments, 234–35
CommitTrans method, 132, 165
common key columns, 3
concurrency issues, 153–56
configuration of virtual directories using scripts, 62–63
Configure SQL XML Support In IIS, 50–51
   advanced options, 60
   Allow POST option, 79
   Allow Template Queries option, 72
   Allow URL Queries option, 79
   Allow XPath option, 99–100
   authentication, 53–55
   caching, disabling, 102
   data access settings, 57–58
   data source specification, 56
   encryption, 55–56
   IIS Virtual Directory Management For SQL Server, 51
   naming new virtual directories, 51–52
   paths, setting, 51–52
   POST queries, 58
   publishing new virtual directories, 51
   security, setting, 52–56
   selecting authentication models, 55
   SQLISAPI DLL location, 60
   template queries, 57–58
   URL queries, allowing, 57
   virtual names, 58–59
   XPath queries, 58
Connection object, 39
   BeginTrans method, 132
   CommitTrans method, 132, 165
   RollbackTrans method, 165
Connect method, 61
constant elements, 104–5
constraints, 160–61
content attribute, 256
content type
   SQL queries, 67–68
   templates, default, 76
   URL, specifying in, 211
contenttype parameter, 76, 211
context nodes, 239
cookies, 186–89
CREATE keyword
   PROC, 176
   TABLE, 130 (see also XML bulk load component, creating tables with)
   VIEW, 190
criteria, column. See constraints
cross-platform compatibility, 4, 10
currency values, 149

D
data, 2
database objects, virtual names for, 59
databases, 2, 39
data processing, 1
datatype annotation, 116–17, 168–69
datatype property (XML), 136
data types
   maximum sizes, 120
   specifying in XDR schemas, 258–60
   support, 140
datatypes namespace, 258
default attribute, 257
default mappings, 89–90, 142
defining documents. See schemas
delegation, 54
DELETE operations. See updategrams, deleting data
deleting
   data, 152–53
   databases, 169–70
   tables, 169–70
delivery company e-commerce example, 211–12
descendant keyword, 239
Dialect property, 40, 97
directives, 20–23
   cdata, 31
   element, 22–23
   hide, 22, 28–29
   ID, 32–33
   IDREF, 32–33
   IDREFS, 32–33
   supported by FOR XML EXPLICIT queries, 22
   xml, 22–23
   xmltext, 22, 29–31
Disconnect method, 61
documents
   attribute-centric, 6
   nested, 7–8
   XML, 5
dollar sign ($), 100, 144–45, 149
DOM (Document Object Model)
   BizTalk Server 2000, 225
   nodeFromID method, 112
downloading data, 213
dropping existing tables, 169–70
DTDs (Document Type Definitions), 86, 232, 252
dt:type attribute, 149
duplicate keys, 161–62

E
e-commerce
   catalog update example, 212–15
   delivery company example, 211–12
   invoicing retailer (see invoicing retailer example)
   online retail Web site, sample (see retail Web site, sample)
   sample scenario, 171–74
   supplier order processing example (see order processing example)
edge tables, 135–36
EDI (Electronic Data Interchange), 4, 223
element-centric mapping, 6
   AUTO mode, 19
   flag parameter, 122, 126
element directive, 22–23
element keyword, 253
ElementName parameter, 20
elements, 5–6
   aliasing, 17
   AUTO mode, 16–19
   ELEMENTS option, 18–19
   empty, 231
   HTML, 229
   limiting occurrences with schemas, 257–58
   prefixes for namespaces, 237
   XML, 230–31
   XPath expressions of, 238
ELEMENTS option, 14, 18–19
ElementType keyword, 253
empty elements, 231
encapsulation of SQL queries. See templates
encryption
   connections, 189
   virtual directories (IIS), 55–56
END TRAN, 132
entities, 2–3
   attributes, 5–6
   characteristics, 2, 5–6
   instances of, 2, 5
   properties, 2
   representation by XML, 5–9
ErrorLogFile property, 165–66
errors
   parseError property, 260
   updategrams, 148
   validating documents, 260
   XML bulk load component, 165–66
EXECUTE stored procedures, 68–69
EXPLICIT mode, 14, 19–32
   bide directive, 22
   cdata directive, 22, 31
   Directive parameter, 20–23
   element directive, 22–23
   ID directive, 22, 32–33
   IDREF directive, 32–33
   IDREFS directive, 32–33
   JOIN operator with, 24
   overflow columns, 29–31
   parent/child data, 25–28
   UNION ALL operator, 26–27
   universal tables, 19–21
   XMLDATA option, 32–33
   xml directive, 22–23
   xmltext directive, 22
extensibility of XML, 230

F
field attribute, 91
fields. See columns
filtering, mapping schemas for, 109–10
flags parameter, 122, 126, 134–35
folders, virtual names, 58–59
ForceTableLock property, 163–64
for-each command, 344–46
foreign-key attribute, 92
foreign keys
   duplicate keys, 161–62
   foreign-relation attribute, 92
   IDENTITY columns, 150–51, 162
foreign-relation attribute, 92
formatting output, 47
FOR XML queries. See SELECT FOR XML
fragments, XML, 232
functoids, 222

G
GROUP BY, 19
guid attribute, 152
GUIDs (globally unique identifiers)
   MSSQLXML dialect, 40
   retail Web site customers, 185–86
   updategrams, 151–52

H
header element, 145–46
headers
   NULL value, 140
   nullvalue attribute, 145–46
   retrieving, 134–35
   template, 73
hide directive, 22, 28–29
high concurrency protection, 154–55
HTML (Hypertext Markup Language), 229–30
   forms, posting templates from, 79–81
   templates with style sheets for, 73–75
   translation from XML, 9–10 (see also style sheets)
HTTP data access, 49–69
   client data request methods, 50
   mapping schemas, retrieving over, 99–101
   POST queries, 58
   scripts, virtual directory configuration, 60–64
   security, setting, 52–56
   stored procedures, 68–69
   testing virtual directories, 64–69
   Virtual Directory Management objects, 61–64
   without FOR XML clauses, 68
HTTP POST queries, 58
Hypertext Markup Language. See HTML (Hypertext Markup Language)

I
ID annotation, 110–13
id attribute, 101, 146–47, 168–69
ID data type, 8–9, 110
ID directive, 22, 32–33
IDENTIFY columns, 150–51
   primary keys, 150–51, 162
   XML bulk load component, 162–63
IDENTIFY_INSERT option, 130–32
iDoc parameter, 122
id-prefix annotation, 110–13
IDREF annotation, 110–13
IDREF data type, 8–9, 110
IDREF directive, 22, 32–33
IDREFS annotation, 110–13
IDREFS data type, 8–9, 110
IDREFS directive, 22, 32–33
ID values in XML documents, 110
IgnoreDuplicateKeys property, 162
IInterchange interface, 224–25
IIS (Microsoft Internet Information Services)
   AddVirtualDirectory method, 62
   authentication, 53–55
   Configure SQL XML Support In IIS, 50–51
   Connect method, 61
   data access settings, 57–58
   data source specification, 56
   Disconnect method, 61
   encryption, 55–56
   HTTP data access with, 49
   security settings, 52–56
   SQLISAPI, 49–51
   SQLVDirControl object, 61
   SQLVDir object, 62–63
   SQLVDirs collection object, 62
   testing virtual directories, 64–69
   URL queries, 64–69
   virtual directories, 50–64
   Virtual Directory Management objects, 61–64
   VirtualName object, 64
   virtual names, 63–64
   VirtualNames collection object, 63
   Windows integrated security, 54–55
IIS Virtual Directory Management For SQL Server, 51
images
   binary data retrieval, 33–34
   Transaction property, 164–65
impersonation, 54
impersonation/delegation model, 53–55
importing catalog example, 215
importing XML data. See XML bulk load component
indexes, losing, 215
inline schemas, 101
inserting XML data, 119–37. See also updategrams; XML bulk load component
   column patterns, 127–28
   creating new tables, 132–33
   edge tables, 135–36
   elements, defining for return, 122
   existing tables, inserting into, 129–32
   flags parameter, 134–35
   IDENTIFY_INSERT option, 130–32
   metadata, retrieving, 133–36
   node tree creation, 120–21
   node tree memory reclamation, 121–22
   order processing example, 201–2
   overflow columns, 134–35
   parsing required, 120–21
   row pattern specification, 123–26
   rows, 131, 148–52
   rowsets, returning, 121–29
   rowset structure definition, 123
   sp_xml_preparedocument stored procedure, 120–21
   sp_xml_removedocument stored procedure, 121–22
   stored procedures for, 120
   xmltext property, 134–35
INSERT statement, 129–32. See also updategrams, inserting rows
installing XML For SQL Server 2000 Web Release 1, 139
integration, business, 3–4
intermediate concurrency protection, 154–55
Internet Explorer. See Microsoft Internet Explorer
Internet Information Services. See IIS (Microsoft Internet Information Services)
intrinsic mapping, 142
invoicing retailer example, 216–26
   BizTalk Editor, 216–21
   channel used, 224–25
   defining business documents, 216–21
   document submission, 224–25
   invoice data insertion, 226
   invoice schema, 219–21
   map creation, 221–23
   messaging, 216–24
   messaging architecture creation, 223–24
   port used, 224
   purchase order schema, 217–19
   receiving documents, 225–26
ISAPI. See SQLISAPI
is-constant annotation, 104–5
is-mapping-schema attribute, 101

J
joins
   annotations, using for, 92
   AUTO mode, 17–18
   EXPLICIT mode with, 24–28
   RAW mode, 15–16

K
KeepIdentify property, 162
KeepNulls property, 163
key attribute, 92
key fields, specifying with annotations, 106–9
key-fields annotation, 106–9, 116
key-relation attribute, 92

L
limit-field annotation, 109–10
limit-value annotation, 109–10
load balancing, 186
locking, 153–56, 163–64
logging XML bulk load component errors, 165–66
logical models of businesses, 2–3
logins, 186
looping, style sheet, 344–46
low concurrency protection, 154

M
map-field annotation, 105–6
mapping, intrinsic, 142
mapping data using updategrams, 142–43
mapping-schema annotation, 143
mapping-schema attribute, 100–101
Mapping Schema property, 46–47, 96
mapping schemas, 85–117
   $ (dollar sign), 100
   ADO with, 95–99
   Allow XPath option, 99–100
   annotations, 90–93
   Base Path property, 96
   binary data, retrieving, 116–17
   bin.base64 XML data type, 116–17
   caching, 100–101
   CDATA retrieval, 117
   closed models, 88
   Command object, 98
   constant elements, 103–5
   content attribute, 90
   currency parameters, passing, 149
   datatype annotation, 116–17
   default mappings, 89–90
   defined, 89
   element-centric mapping, 90
   excluding fields from, 105–6
   field attribute, 91
   filtering values, 109–10
   foreign-key attribute, 92
   foreign-relation attribute, 92
   HTTP, retrieving over, 99–101
   ID annotation, 110–13
   id attribute, 101
   id-prefix annotation, 110–13
   IDREF annotation, 110–13
   IDREFS annotation, 110–13
   inline schemas, 101
   is-mapping-schema attribute, 101
   key attribute, 92
   key-fields annotation, 106–9, 116
   key field specification, 106–9
   key-relation attribute, 92
   limit-field annotation, 109–10
   limit-value annotation, 109–10
   map-field annotation, 105–6
   mapping-schema attribute, 100–101
   Mapping Schema property, 96
   to multiple tables, 92–93
   namespaces, specifying, 113–14
   nodeFromID method, 113
   overflow columns, 117–18
   overflow-field annotation, 117–18
   primary key field specification, 106–9
   purchase order creation, 198–99
   ReadText method, 97–98
   receiving results, 97–98
   relation attribute, 91
   relationship annotation, 109
   relationship element, 92–93, 106–7
   retail Web site, sample, 179–80
   retrieving data with, 94–102
   root element specification, 96–97, 100
   to single tables, 91
   specifying schemas in ADO, 96
   SS_STREAM_FLAGS property, 101
   Stream object, 97–98
   style sheets, applying, 98–99
   target-namespace annotation, 113–14
   templates, referencing, 100–101
   textOnly value, 90
   updategrams with, 143
   url-encode annotation, 116–17
   URLs, accessing through, 99–100
   use-cdata annotation, 117
   virtual directories, 99–101
   virtual names, 99–100
   XDR, 86–88
   XML bulk load component, 157–59, 166–68
   Xml Root property, 96
   XPath expressions, 94–95, 97, 100, 113–14
   xpath-query element, 100–101
   Xsl property, 98
markup tags
   HTML, 229–30
   XML, 230–31 (see also tags)
match parameter, 244
maxOccurs attribute, 257–58
messaging, 216–24
metadata
   columns, 19–21
   edge tables, 135–36
   retrieving, 133–36
metalanguages, 229
metaproperties, 133–34
Microsoft, XML schema standard supported, 5
Microsoft Active Server Pages. See ASP (Microsoft Active Server Pages)
Microsoft ActiveX Data Objects. See ADO (Microsoft ActiveX Data Objects)
Microsoft Internet Explorer
   style sheet namespaces, 242
   validation against schema, 261
Microsoft Internet Information Services. See IIS (Microsoft Internet Information Services)
Microsoft Windows applications, posting templates from, 81–82
Microsoft XML parser
   data type support, 259
   posting templates, 81–82
   style sheet application, 251
   style sheet namespace support, 242
   XDR support, 86
Microsoft XML-SQL namespace. See XML-SQL namespace
minOccurs attribute, 257–58
model attribute, 255
modes, XML format, 13–14
modifying data. See updategrams, modifying records
MSDN Web site, 139
MSSQLError processing instructions, 148
MSSQLXML dialect, 40
MSXML parser. See Microsoft XML parser
multivalue attribute, 144

N
name attribute, 253
namespaces, 235–37
   datatypes, 258
   declaring, 236–37
   default, 237
   is-xml attribute, 140
   nullvalue attribute, 140
   prefixes, 115, 237
   specifying, 113–14
   style sheet, 242
   XDR schemas, 253
   xmlns attribute, 236
   xml-updategram, 141
   x-schema, 260
naming virtual directories, 51–52, 62
nested documents, 7–8
nesting of XML tags, 14, 232, 234
.NET platform, 35
nodeFromID method, 9, 113
nodes, 238–40
nodes, metadata, 135–36
node tree creation, 120–21
nondecomposable units, 2
nonparsed character data, 31
Northwind Traders, 11
NULL
   parameter, 145–46
   use in universal tables, 26–27
   value, setting to, 144
   XML bulk load component, 163
nullvalue attribute, 140, 145–46
nVarchar data type, 120

O
ODBC (Open Database Connectivity), 68–69
OLE-DB API, 35
OLEDB connection string custom settings, 60
ON DELETE CASCADE option, 153
online retail Web site, sample. See retail Web site, sample
open content model, 254–55
opening tags, 232–34
OpenXML function, 119–37
   colpattern parameter, 123
   column patterns, 127–28
   creating new tables, 132–33
   edge tables, 135–36
   existing tables, inserting data, 129–32
   flags parameter, 122, 126, 134–35
   IDENTIFY_INSERT option, 130–32
   iDoc parameter, 122
   inserting data, 129–32
   inserting invoice data example, 226
   metadata, retrieving, 133–36
   node tree creation, 120–21
   node tree memory reclamation, 121–22
   overflow columns, 134–35
   parsing required, 120–21
   root element, 124
   rowpattern parameter, 122–24
   row pattern specification, 123–26
   rows, inserting, 131
   rowsets, returning, 121–29
   rowset schemas, 127–29
   rowset structure definition, 123
   sp_xml_preparedocument stored procedure, 120–21
   sp_xml_removedocument stored procedure, 121–22
   syntax of, 122
   tables specified for structure, 129
   WITH clause, 123
   xmltext property, 134–35
optimistic locking, 153–54
orchestration, 216
ORDER BY operator
   hide directive with, 28–29
   with UNION ALL operator, 27–29
order processing example, 200–211
   exporting order to delivery company, 208–11
   inserting order into database, 201–2
   order picking application, 202–8
   style sheets, 204–7
   templates, 203, 205
   updategram, 210–11
   virtual directory folders, 202
Output Encoding property, 47
OutputStream property, 40–42
output streams, Response object, 47
overflow columns, 22, 29–31, 117–18, 134–35
overflow-field annotation, 117–18


Next




Top of Page


Last Updated: Friday, July 6, 2001