Inserting Data Using XML Updategrams (SQLXML 4.0)

  • Article
  • 15 minutes to read

Applies to: yesSQL Server (all supported versions) YesAzure SQL Database

An updategram indicates an insert operation when a record instance appears in the <after> block only not in the respective <before> block. In this case, the updategram inserts the record in the <subsequently> block into the database.

This is the updategram format for an insert operation:

              <ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">     <updg:sync [mapping-schema="SampleSchema.xml"]  >      [<updg:before>      </updg:earlier>]       <updg:after [updg:returnid="x y ...] >          <ElementName [updg:id="value"]                       [updg:at-identity="ten"]                       [updg:guid="y"]                      attribute="value"                       aspect="value"                      ...          />         [<ElementName .../>... ]       </updg:after>     </updg:sync>   </ROOT>

<before> Block

The <earlier> block can be omitted for an insert operation. If the optional mapping-schema attribute is not specified, the <ElementName> that is specified in the updategram maps to a database table and the kid elements or attributes map to columns in the tabular array.

<after> Block

You can specify one or more records in the <after> block.

If the <after> block does not supply a value for a item column, the updategram uses the default value that is specified in the annotated schema (if a schema has been specified). If the schema does not specify a default value for the cavalcade, the updategram does not specify any explicit value to this column and, instead, assigns the Microsoft SQL Server default value (if specified) to this column. If there is no SQL Server default value and the column accepts a NULL value, the updategram sets the column value to NULL. If the column neither has a default value nor accepts a Nix value, the control fails and the updategram returns an error. The optional updg:returnid attribute is used to return the identity value that is generated by the organization when a record is added in a table with an IDENTITY-type column.

updg:id Attribute

If the updategram is inserting but records, the updategram does not crave the updg:id attribute. For more information most updg:id, run into Updating Data Using XML Updategrams (SQLXML 4.0).

updg:at-identity Attribute

When an updategram inserts a record in a table that has an IDENTITY-type column, the updategram tin capture the system assigned value by using the optional updg:at-identity attribute. The updategram can then use this value in subsequent operations. Upon execution of the updategram, you can render the identity value that is generated by specifying the updg:returnid attribute.

updg:guid Attribute

The updg:guid aspect is an optional aspect that generates a globally unique identifier. This value remains in scope for the entire <sync> block in which it is specified. You can apply this value anywhere in the <sync> block. The attribute calls the NEWGUID()SQL Server function to generate the unique identifier.

Examples

To create working samples using the following examples, yous must run across the requirements specified in Requirements for Running SQLXML Examples.

Before using the updategram examples, note the following:

  • Most of the examples utilise default mapping (that is, no mapping schema is specified in the updategram). For more examples of updategrams that apply mapping schemas, see Specifying an Annotated Mapping Schema in an Updategram (SQLXML 4.0).

  • Near of the examples utilize the AdventureWorks2012 sample database. All the updates are applied to the tables in this database.

A. Inserting a record by using an updategram

This attribute-centric updategram inserts a record in the HumanResources.Employee table in the AdventureWorks2012 database.

In this example, the updategram does non specify a mapping schema. Therefore, the updategram uses default mapping, in which the chemical element name maps to a table name and the attributes or child elements map to columns in that table.

The AdventureWorks2012 schema for the HumanResources.Department table imposes a 'not nil' brake on all columns. Therefore, the updategram must include values specified for all columns. The DepartmentID is an IDENTITY-type column. Therefore, no values are specified for information technology.

              <ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">     <updg:sync >       <updg:before>       </updg:before>       <updg:subsequently>          <HumanResources.Department                Proper noun="New Product Research"                GroupName="Research and Development"                ModifiedDate="2010-08-31"/>       </updg:after>     </updg:sync>   </ROOT>
To exam a sample XPath query confronting the schema

  1. Copy the updategram above and paste it into a text file. Salve the file as MyUpdategram.xml.

  2. Create and use the SQLXML 4.0 Test Script (Sqlxml4test.vbs) to execute the template.

    For more than data, encounter Using ADO to Execute SQLXML 4.0 Queries.

In an element-centric mapping, the updategram looks similar this:

              <ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">     <updg:sync >       <updg:before>       </updg:before>       <updg:after>          <HumanResources.Section>               <Name> New Product Enquiry </Name>               <GroupName> Research and Development </GroupName>               <ModifiedDate>2010-08-31</ModifiedDate>          </HumanResources.Department>       </updg:after>     </updg:sync>   </ROOT>

In a mixed-mode (element-centric and attribute-centric) updategram, an element tin take both attributes and subelements, as shown in this updategram:

              <ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">     <updg:sync >       <updg:before>       </updg:earlier>       <updg:after>          <HumanResources.Section                Name=" New Product Inquiry "                <GroupName>Research and Development</GroupName>               <ModifiedDate>2010-08-31</ModifiedDate>          </HumanResources.Department>       </updg:after>     </updg:sync>   </ROOT>

B. Inserting multiple records by using an updategram

This updategram adds two new shift records to the HumanResources.Shift table. The updategram does non specify the optional <before> block.

              <ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">     <updg:sync>       <updg:afterwards >          <HumanResources.Shift Proper noun="Day-Evening"                           StartTime="1900-01-01 xi:00:00.000"                           EndTime="1900-01-01 19:00:00.000"                           ModifiedDate="2004-01-01 00:00:00.000" />          <HumanResources.Shift Name="Evening-Night"                           StartTime="1900-01-01 nineteen:00:00.000"                           EndTime="1900-01-01 03:00:00.000"                           ModifiedDate="2004-01-01 00:00:00.000" />       </updg:afterward>     </updg:sync>   </ROOT>
To test a sample XPath query against the schema

  1. Copy the updategram above and paste it into a text file. Relieve the file equally Updategram-AddShifts.xml.

  2. Create and use the SQLXML 4.0 Test Script (Sqlxml4test.vbs) to execute the template.

    For more data, come across Using ADO to Execute SQLXML 4.0 Queries.

Another version of this example is an updategram that uses 2 separate <subsequently> blocks instead of one block to insert the 2 employees. This is valid and tin can exist encoded equally follows:

              <ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">     <updg:sync>       <updg:after >          <HumanResources.Shift Proper name="Day-Evening"                           StartTime="1900-01-01 eleven:00:00.000"                           EndTime="1900-01-01 19:00:00.000"                           ModifiedDate="2004-01-01 00:00:00.000" />       </updg:afterwards>       <updg:before>       </updg:earlier>       <updg:after >          <HumanResources.Shift Name="Evening-Night"                           StartTime="1900-01-01 nineteen:00:00.000"                           EndTime="1900-01-01 03:00:00.000"                           ModifiedDate="2004-01-01 00:00:00.000" />       </updg:after>     </updg:sync>   </ROOT>

C. Working with valid SQL Server characters that are not valid in XML

In SQL Server, table names tin include a space, such every bit the Order Details table in the Northwind database. Even so, this is not valid in XML characters that are valid SQL Server identifiers but not valid XML identifiers tin be encoded using '__xHHHH__' as the encoding value, where HHHH stands for the four-digit hexadecimal UCS-2 code for the grapheme in the well-nigh significant bit-first social club.

Annotation

This example uses the Northwind database. You can install the Northwind database past using an SQL script available for download from this Microsoft Spider web site.

Also, the element name must exist enclosed inside brackets ([ ]). Because the characters [and] are non valid in XML, you must encode them as _x005B_ and _x005D_, respectively. (If you use a mapping schema, you lot can provide element names that do not contain characters that are non valid, such as white spaces. The mapping schema does the necessary mapping; therefore, you do not need to encode for these characters).

This updategram adds a tape to the Order Details table in the Northwind database:

              <ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">     <updg:sync >       <updg:before>       </updg:before>       <updg:after>         <_x005B_Order_x0020_Details_x005D_ OrderID="1"               ProductID="eleven"               UnitPrice="$1.0"               Quantity="i"               Discount="0.0" />       </updg:subsequently>     </updg:sync>   </ROOT>

The UnitPrice cavalcade in the Order Details table is of the money type. To apply the advisable type conversion (from a cord type to a coin type), the dollar sign graphic symbol ($) must exist added as part of the value. If the updategram does not specify a mapping schema, the first character of the string value is evaluated. If the get-go graphic symbol is a dollar sign ($), the appropriate conversion is applied.

If the updategram is specified confronting a mapping schema where the column is appropriately marked as either dt:type="fixed.fourteen.4" or sql:datatype="money", the dollar sign ($) is not required and the conversion is handled by the mapping. This is the recommended way to ensure that the appropriate type conversion occurs.

To test a sample XPath query against the schema

  1. Copy the updategram above and paste it into a text file. Save the file as UpdategramSpacesInTableName.xml.

  2. Create and use the SQLXML four.0 Exam Script (Sqlxml4test.vbs) to execute the template.

    For more information, see Using ADO to Execute SQLXML iv.0 Queries.

D. Using the at-identity attribute to retrieve the value that has been inserted in the IDENTITY-type cavalcade

The post-obit updategram inserts two records: 1 in the Sales.SalesOrderHeader table and another in the Sales.SalesOrderDetail table.

First, the updategram adds a tape to the Sales.SalesOrderHeader table. In this tabular array, the SalesOrderID column is an IDENTITY-type column. Therefore, when you add this record to the table, the updategram uses the at-identity attribute to capture the assigned SalesOrderID value as "x" (a placeholder value). The updategam and then specifies this at-identity variable as the value of SalesOrderID aspect in the <Sales.SalesOrderDetail> element.

              <ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">    <updg:sync >     <updg:before>     </updg:before>     <updg:subsequently>      <Sales.SalesOrderHeader updg:at-identity="x"                 RevisionNumber="one"                OrderDate="2001-07-01 00:00:00.000"                DueDate="2001-07-13 00:00:00.000"                OnlineOrderFlag="0"                CustomerID="676"                ContactID="378"                BillToAddressID="985"                ShipToAddressID="985"                ShipMethodID="5"                SubTotal="24643.9362"                TaxAmt="1971.5149"                Freight="616.0984"                rowguid="00001111-2222-3333-4444-556677889900"                ModifiedDate="2001-07-08 00:00:00.000" />         <Sales.SalesOrderDetail SalesOrderID="x"                   LineNumber="one"                   OrderQty="i"                   ProductID="776"                   SpecialOfferID="1"                   UnitPrice="2429.9928"                   UnitPriceDiscount="0.00"                   rowguid="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"                   ModifiedDate="2001-07-01 00:00:00.000" />       </updg:after>     </updg:sync>   </ROOT>

If yous want to return the identity value that is generated by the updg:at-identity attribute, you lot can use the updg:returnid attribute. The following is a revised updategram that returns this identity value. (This updategram adds 2 order records and ii social club particular records, just to make the case a niggling more than circuitous.)

              <ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">    <updg:sync>     <updg:before>     </updg:before>     <updg:after updg:returnid="x y" >          <HumanResources.Shift updg:at-identity="x" Name="24-hour interval-Evening"                           StartTime="1900-01-01 xi:00:00.000"                           EndTime="1900-01-01 19:00:00.000"                           ModifiedDate="2004-01-01 00:00:00.000" />          <HumanResources.Shift updg:at-identity="y" Name="Evening-Night"                           StartTime="1900-01-01 nineteen:00:00.000"                           EndTime="1900-01-01 03:00:00.000"                           ModifiedDate="2004-01-01 00:00:00.000" />     </updg:subsequently>    </updg:sync>   </ROOT>

When the updategram is executed, information technology returns results like to the following, which includes the identity value (the generated value of the ShiftID cavalcade used for table identity) that was generated:

              <ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">      <returnid>        <x>4</x>        <y>5</y>      </returnid>    </ROOT>
To exam a sample XPath query confronting the schema

  1. Copy the updategram to a higher place and paste it into a text file. Salvage the file as Updategram-returnId.xml.

  2. Create and utilize the SQLXML 4.0 Examination Script (Sqlxml4test.vbs) to execute the template.

    For more information, come across Using ADO to Execute SQLXML 4.0 Queries.

Eastward. Using the updg:guid attribute to generate a unique value

In this instance, the updategram inserts a tape in the Cust and CustOrder tables. Also, the updategram generates a unique value for the CustomerID attribute by using the updg:guid attribute.

              <ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">     <updg:sync >       <updg:earlier>       </updg:before>       <updg:later updg:returnid="x" >         <Cust updg:guid="x" >            <CustID>ten</CustID>            <LastName>Fuller</LastName>         </Cust>         <CustOrder>            <CustID>10</CustID>            <OrderID>1</OrderID>         </CustOrder>       </updg:later>     </updg:sync>   </ROOT>

The updategram specifies the returnid attribute. Every bit a result, the GUID that is generated is returned:

              <ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">     <returnid>       <x>7111BD1A-7F0B-4CEE-B411-260DADFEFA2A</x>      </returnid>   </ROOT>
To examination the updategram

  1. Copy the updategram above and paste information technology into a text file. Salve the file as Updategram-GenerateGuid.xml.

  2. Create these tables:

                      Utilize tempdb   CREATE TABLE Cust (CustID uniqueidentifier, LastName varchar(20))   CREATE TABLE CustOrder (CustID uniqueidentifier, OrderID int)

  3. Create and use the SQLXML 4.0 Test Script (Sqlxml4test.vbs) to execute the template.

    For more information, see Using ADO to Execute SQLXML 4.0 Queries.

F. Specifying a schema in an updategram

The updategram in this case inserts a record into the following table:

              CustOrder(OrderID, EmployeeID, OrderType)

An XSD schema is specified in this updategram (that is, in that location is no default mapping of updategram elements and attributes). The schema provides the necessary mapping of the elements and attributes to the database tables and columns.

The following schema (CustOrderSchema.xml) describes a <CustOrder> element that consists of the OrderID and EmployeeID attributes. To make the schema more interesting, a default value is assigned to the EmployeeID attribute. An updategram uses an aspect's default value only for insert operations, and then only if the updategram does not specify that attribute.

              <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"               xmlns:sql="urn:schemas-microsoft-com:mapping-schema">     <xsd:element name="CustOrder" >      <xsd:complexType>           <xsd:attribute proper noun="OrderID"     type="xsd:integer" />            <xsd:aspect name="EmployeeID"  type="xsd:integer" />           <xsd:aspect proper noun="OrderType  " type="xsd:integer" default="one"/>       </xsd:complexType>     </xsd:element>   </xsd:schema>

This updategram inserts a record into the CustOrder table. The updategram specifies simply the OrderID and EmployeeID attribute values. It does not specify the OrderType attribute value. Therefore, the updategram uses the default value of the EmployeeID aspect that is specified in the preceding schema.

              <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"                xmlns:updg="urn:schemas-microsoft-com:xml-updategram">   <updg:sync mapping-schema='CustOrderSchema.xml'>   <updg:after>          <CustOrder OrderID="98000" EmployeeID="ane" />   </updg:afterward>   </updg:sync>   </ROOT>

For more examples of updategrams that specify a mapping schema, see Specifying an Annotated Mapping Schema in an Updategram (SQLXML 4.0).

To test the updategram

  1. Create this tabular array in the tempdb database:

                      Utilize tempdb   CREATE TABLE CustOrder(                    OrderID int,                     EmployeeID int,                     OrderType int)

  2. Copy the schema above and paste it into a text file. Relieve the file as CustOrderSchema.xml.

  3. Copy the updategram above and paste it into a text file. Save the file every bit CustOrderUpdategram.xml in the same folder used in the previous footstep.

  4. Create and use the SQLXML 4.0 Exam Script (Sqlxml4test.vbs) to execute the updategram.

    For more data, see Using ADO to Execute SQLXML 4.0 Queries.

This is the equivalent XDR schema:

              <Schema xmlns="urn:schemas-microsoft-com:xml-information"           xmlns:sql="urn:schemas-microsoft-com:xml-sql">    <ElementType proper name="CustOrder" >       <AttributeType proper noun="OrderID" />       <AttributeType name="EmployeeID" />       <AttributeType proper noun="OrderType" default="ane" />       <aspect type="OrderID"  />       <attribute type="EmployeeID" />       <aspect type="OrderType" />     </ElementType>   </Schema>

G. Using the xsi:nil attribute to insert null values in a column

If you want to insert a null value in the corresponding column in the table, y'all tin can specify the xsi:nil attribute on an element in an updategram. In the corresponding XSD schema, the XSD nillable attribute likewise must be specified.

For case, consider this XSD schema:

              <?xml version="one.0"?>   <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"                xmlns:sql="urn:schemas-microsoft-com:mapping-schema">   <xsd:element name="Student" sql:relation="Students">     <xsd:complexType>       <xsd:all>         <xsd:element proper name="fname" sql:field="first_name"                                    type="xsd:string"                                     nillable="true"/>       </xsd:all>       <xsd:aspect proper name="SID"                            sql:field="StudentID"                           type="xsd:ID"/>           <xsd:attribute name="lname"                                sql:field="last_name"                           type="xsd:string"/>       <xsd:attribute name="minitial"                             sql:field="middle_initial"                            blazon="xsd:string"/>       <xsd:aspect name="years"                                 sql:field="no_of_years"                            blazon="xsd:integer"/>     </xsd:complexType>    </xsd:element>   </xsd:schema>

The XSD schema specifies nillable="true" for the <fname> element. The post-obit updategram uses this schema:

              <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"         xmlns:updg="urn:schemas-microsoft-com:xml-updategram"         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">      <updg:sync mapping-schema='StudentSchema.xml'>     <updg:earlier/>     <updg:after>       <Student SID="S00004" lname="Elmaci" minitial="" years="2">         <fname xsi:nil="true">       </fname>       </Student>     </updg:after>   </updg:sync>      </ROOT>

The updategram specifies xsi:null for the <fname> element in the <later> cake. Therefore, when this updategram is executed, a value of Zip is inserted for the first_name column in the table.

To test the updategram

  1. Create the following table in the tempdb database:

                      USE tempdb   CREATE Table Students (      StudentID char(6)NOT NULL ,      first_name varchar(50),      last_name varchar(fifty),      middle_initial char(ane),      no_of_years int Null)   GO

  2. Copy the schema above and paste it into a text file. Salve the file as StudentSchema.xml.

  3. Copy the updategram higher up and paste it into a text file. Save the file as StudentUpdategram.xml in the same binder used in previous step to relieve StudentSchema.xml.

  4. Create and utilise the SQLXML 4.0 Exam Script (Sqlxml4test.vbs) to execute the updategram.

    For more information, run across Using ADO to Execute SQLXML 4.0 Queries.

H. Specifying namespaces in an updategram

In an updategram you can have elements that belong to a namespace alleged in the same element in the updategram. In this case, the corresponding schema must too declare the same namespace and the element must belong to that target namespace.

For case, in the following updategram (UpdateGram-ElementHavingNamespace.xml), the <Order> element belongs to a namespace alleged in the chemical element.

              <ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">     <updg:sync mapping-schema='XSD-ElementHavingNameSpace.xml'>       <updg:after>          <x:Order  xmlns:x="https://server/xyz/schemas/"                updg:at-identity="SalesOrderID"                 RevisionNumber="1"                OrderDate="2001-07-01 00:00:00.000"                DueDate="2001-07-13 00:00:00.000"                OnlineOrderFlag="0"                CustomerID="676"                ContactID="378"                BillToAddressID="985"                ShipToAddressID="985"                ShipMethodID="5"                SubTotal="24643.9362"                TaxAmt="1971.5149"                Freight="616.0984"                rowguid="00009999-8888-7777-6666-554433221100"                ModifiedDate="2001-07-08 00:00:00.000" />       </updg:after>     </updg:sync>   </ROOT>

In this case, the schema must also declare the namespace as shown in this schema:

The post-obit schema (XSD-ElementHavingNamespace.xml) shows how the corresponding element and attributes must exist declared.

              <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"         xmlns:dt="urn:schemas-microsoft-com:datatypes"         xmlns:sql="urn:schemas-microsoft-com:mapping-schema"          xmlns:x="https://server/xyz/schemas/"         targetNamespace="https://server/xyz/schemas/" >     <xsd:element name="Order" sql:relation="Sales.SalesOrderHeader" type="x:Order_type"/>     <xsd:complexType name="Order_type">       <xsd:aspect name="SalesOrderID"  type="xsd:ID"/>       <xsd:attribute name="RevisionNumber" type="xsd:unsignedByte"/>       <xsd:attribute name="OrderDate" blazon="xsd:dateTime"/>       <xsd:aspect name="DueDate" blazon="xsd:dateTime"/>       <xsd:attribute proper noun="ShipDate" type="xsd:dateTime"/>       <xsd:attribute name="Condition" type="xsd:unsignedByte"/>       <xsd:attribute name="OnlineOrderFlag" type="xsd:boolean"/>       <xsd:aspect name="SalesOrderNumber" blazon="xsd:cord"/>       <xsd:attribute proper noun="PurchaseOrderNumber" type="xsd:string"/>       <xsd:aspect name="AccountNumber" type="xsd:string"/>       <xsd:attribute name="CustomerID" type="xsd:int"/>       <xsd:attribute name="ContactID" blazon="xsd:int"/>       <xsd:aspect name="SalesPersonID" type="xsd:int"/>       <xsd:attribute proper name="TerritoryID" type="xsd:int"/>       <xsd:attribute name="BillToAddressID" type="xsd:int"/>       <xsd:attribute proper noun="ShipToAddressID" blazon="xsd:int"/>       <xsd:attribute name="ShipMethodID" type="xsd:int"/>       <xsd:attribute name="CreditCardID" type="xsd:int"/>       <xsd:attribute name="CreditCardApprovalCode" type="xsd:string"/>       <xsd:attribute name="CurrencyRateID" blazon="xsd:int"/>       <xsd:attribute name="SubTotal" blazon="xsd:decimal"/>       <xsd:attribute proper noun="TaxAmt" type="xsd:decimal"/>       <xsd:attribute proper name="Freight" type="xsd:decimal"/>       <xsd:attribute name="TotalDue" type="xsd:decimal"/>       <xsd:attribute proper noun="Comment" type="xsd:string"/>       <xsd:attribute proper noun="rowguid" type="xsd:string"/>       <xsd:attribute name="ModifiedDate" type="xsd:dateTime"/>     </xsd:complexType>   </xsd:schema>
To test the updategram

  1. Copy the schema above and paste it into a text file. Relieve the file as XSD-ElementHavingNamespace.xml.

  2. Re-create the updategram above and paste information technology into a text file. Save the file equally Updategram-ElementHavingNamespace.xml in the aforementioned folder used to save XSD-ElementHavingnamespace.xml.

  3. Create and use the SQLXML iv.0 Test Script (Sqlxml4test.vbs) to execute the updategram.

    For more information, see Using ADO to Execute SQLXML four.0 Queries.

I. Inserting data into an XML data type column

The xml data type was introduced in SQL Server 2005 (ix.x). Y'all can use updategrams to insert and update data stored in xml information type columns with the following provisions:

  • The xml cavalcade cannot be used for identifying an existing row. Therefore, it cannot exist included in the updg:before section of an updategram.

  • Namespaces that are in telescopic of the XML fragment inserted into the xml cavalcade will exist preserved and their namespace declarations are added to the top chemical element of the inserted fragment.

For example, in the following updategram (SampleUpdateGram.xml), the <Desc> element updates the ProductDescription column in the Production>productModel table in the AdventureWorks2012 sample database. The event of this updategram is that the XML contents of the ProductDescription column are update with the XML contents of the <Desc> element.

              <ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">       <updg:sync mapping-schema="SampleSchema.xml" >          <updg:before>   <ProductModel ProductModelID="19">      <Proper noun>Mountain-100</Name>   </ProductModel>       </updg:before>       <updg:later>    <ProductModel>       <Name>Mountain-100</Name>       <Desc><?xml-stylesheet href="ProductDescription.xsl" blazon="text/xsl"?>           <p1:ProductDescription xmlns:p1="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"                  xmlns:wm="https://schemas.microsoft.com/sqlserver/2004/07/take a chance-works/ProductModelWarrAndMain"                  xmlns:wf="https://world wide web.adventure-works.com/schemas/OtherFeatures"                  xmlns:html="http://www.w3.org/1999/xhtml"                  xmlns="">     <p1:Summary>        <html:p>Insert Case</html:p>     </p1:Summary>     <p1:Manufacturer>       <p1:Name>AdventureWorks</p1:Name>       <p1:Copyright>2002</p1:Copyright>       <p1:ProductURL>HTTP://www.Run a risk-works.com</p1:ProductURL>     </p1:Manufacturer>     <p1:Features>These are the production highlights.        <wm:Warranty>          <wm:WarrantyPeriod>iii years</wm:WarrantyPeriod>          <wm:Description>parts and labor</wm:Description>       </wm:Warranty>       <wm:Maintenance>          <wm:NoOfYears>10 years</wm:NoOfYears>          <wm:Description>maintenance contract available through your dealer or whatever AdventureWorks retail store.</wm:Clarification>       </wm:Maintenance>       <wf:cycle>High operation wheels.</wf:cycle>       <wf:saddle>         <html:i>Anatomic pattern</html:i> and made from durable leather for a full-twenty-four hours of riding in condolement.</wf:saddle>       <wf:pedal>          <html:b>Top-of-the-line</html:b> clipless pedals with adjustable tension.</wf:pedal>       <wf:BikeFrame>Each frame is mitt-crafted in our Bothell facility to the optimum diameter and wall-thickness required of a premium mountain frame. The oestrus-treated welded aluminum frame has a larger bore tube that absorbs the bumps.</wf:BikeFrame>       <wf:crankset> Triple crankset; alumunim crank arm; flawless shifting. </wf:crankset>      </p1:Features>      <p1:Picture>         <p1:Angle>front</p1:Angle>         <p1:Size>minor</p1:Size>         <p1:ProductPhotoID>118</p1:ProductPhotoID>      </p1:Film>      <p1:Specifications> These are the product specifications.        <Cloth>Almuminum Blend</Material>        <Color>Available in near colors</Color>        <ProductLine>Mountain wheel</ProductLine>        <Style>Unisex</Manner>        <RiderExperience>Advanced to Professional riders</RiderExperience>      </p1:Specifications>     </p1:ProductDescription>    </Desc>         </ProductModel>       </updg:after>     </updg:sync>   </ROOT>

The updategram refers to the following annotated XSD schema (SampleSchema.xml).

              <?xml version="1.0" encoding="utf-8" ?>   <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"               xmlns:sql="urn:schemas-microsoft-com:mapping-schema"              xmlns="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription">      <xsd:chemical element name="ProductModel"  sql:relation="Product.ProductModel" >        <xsd:complexType>          <xsd:sequence>             <xsd:element name="Name" type="xsd:string"></xsd:element>             <xsd:element proper noun="Desc" sql:field="CatalogDescription" sql:datatype="xml">              <xsd:complexType>               <xsd:sequence>                 <xsd:chemical element name="ProductDescription">                    <xsd:complexType>                      <xsd:sequence>                        <xsd:chemical element proper noun="Summary" type="xsd:anyType">                        </xsd:element>                      </xsd:sequence>                    </xsd:complexType>                 </xsd:element>               </xsd:sequence>              </xsd:complexType>             </xsd:element>           </xsd:sequence>          <xsd:aspect proper noun="ProductModelID" sql:field="ProductModelID"/>        </xsd:complexType>     </xsd:element>   </xsd:schema>
To examination the updategram

  1. Copy the schema above and paste it into a text file. Salve the file as XSD-SampleSchema.xml.

    Note

    Considering updategrams back up default mapping, in that location is no way to place the commencement and catastrophe of the xml data type. This effectively means that a mapping schema is required when inserting or updating tables with xml data type columns. When a schema is not provided, SQLXML returns an mistake indicating that one of the columns is missing from the table.

  2. Copy the updategram above and paste information technology into a text file. Save the file as SampleUpdategram.xml in the same folder used to save SampleSchema.xml.

  3. Create and utilise the SQLXML iv.0 Test Script (Sqlxml4test.vbs) to execute the updategram.

    For more data, meet Using ADO to Execute SQLXML 4.0 Queries.

See Likewise

Updategram Security Considerations (SQLXML four.0)