Windows LiveWindows Live™
 
 
jose's profileSQL Server 2005 blogsBlogLists Tools Help

Blog
    September 23

    Loading XML to SQL SERVER 2005

    Bulk importing XML as a binary byte stream

    When bulk loading XML from a file that contains an encoding declaration that you want to apply, specify the SINGLE_BLOB in OPENROWSET. This option ensures that the XML parser in SQL Server imports the data according to the encoding scheme specified in the XML declaration. For example, the following INSERT statement inserts an XML instance in a single column table.
     
    USE tempdb
    CREATE TABLE T (IntCol int, XmlCol xml)
    GO
    INSERT INTO T(XmlCol)
    SELECT * FROM OPENROWSET(
       BULK 'c:\SampleFolder\SampleData3.txt',
       SINGLE_BLOB) AS x
     
    To run this bulk load, create a utf-8 encoded file (c:\SampleFolder\SampleData3.txt) with the following sample instance that specifies the UTF-8 encoding scheme and then execute the INSERT statement.
     
    <?xml version="1.0" encoding="UTF-8"?>
    <Root>
              <ProductDescription ProductModelID="5">
                 <Summary>Some Text</Summary>
              </ProductDescription>
    </Root>
     
    By using SINGLE_BLOB in this way, you can avoid a mismatch between the encoding of the XML document (as specified by the XML encoding declaration) and the string codepage implied by the server.
    1:35 AM | Add a comment | Read comments (4) | Permalink | Blog it
    September 19

    XML DATATYPE (series 1)

    New to SQL Server 2005, is The XML data type, which  lets you store XML documents and fragments in a SQL Server database. An XML fragment is an XML instance that is missing a single top-level element. You can create columns and variables of the XML type and store XML instances in them. Note that the stored representation of XML data type instances cannot exceed 2 GB.

    Where can you use the XML data type
    a. Table
    b. Variable
    c. Parameters
    d. Return of a function
     
    To do: Store XML to a table

    a. Create a Table with XML data type
    CREATE TABLE ProductDocs (ID INT IDENTITY PRIMARY KEY,
    ProductDoc XML NOT NULL)
    GO
     
    b. Store XML data  Into the table
    INSERT INTO ProductDocs VALUES('
    <Product>
    <ProductID>1</ProductID>
    <ProductName>Chai</ProductName>
    <SupplierID>1</SupplierID>
    <CategoryID>1</CategoryID>
    <QuantityPerUnit>10 boxes x 20   bags</QuantityPerUnit>
    <UnitPrice>18.0000</UnitPrice>
    <UnitsInStock>39</UnitsInStock>
    <UnitsOnOrder>0</UnitsOnOrder>
    <ReorderLevel>10</ReorderLevel>
    <Discontinued>0</Discontinued>
    </Product>
    ')

    c. Retrieve XMLdoc
    Select product from productdocs

    D. create an XML Schema
    CREATE XML SCHEMA COLLECTION ProductSchema AS '
    <xs:schema xmlns:xs="
    http://www.w3.org/2001/XMLSchema"
    targetNamespace="    http://www.microsoft.com/schemas/adventureworks/
    products"
    xmlns:prod="    http://www.microsoft.com/schemas/adventureworks/
    products">
    <xs:element name="Product">
    <xs:complexType>
    <xs:sequence>
    <xs:element ref="prod:ProductID" />
    <xs:element ref="prod:ProductName" />
    <xs:element ref="prod:SupplierID" />
    <xs:element ref="prod:CategoryID" />
    <xs:element ref="prod:QuantityPerUnit" />
    <xs:element ref="prod:UnitPrice" />
    <xs:element ref="prod:UnitsInStock" />
    <xs:element ref="prod:UnitsOnOrder" />
    <xs:element ref="prod:ReorderLevel" />
    <xs:element ref="prod:Discontinued" />
    </xs:sequence>
    </xs:complexType>
    </xs:element>
    <xs:element name="ProductID" type="xs:integer" />
    <xs:element name="ProductName" type="xs:string" />
    <xs:element name="SupplierID" type="xs:integer" />
    <xs:element name="CategoryID" type="xs:integer" />
    <xs:element name="QuantityPerUnit" type="xs:string" />
    <xs:element name="UnitPrice" type="xs:double" />
    <xs:element name="UnitsInStock" type="xs:integer" />
    <xs:element name="UnitsOnOrder" type="xs:integer" />
    <xs:element name="ReorderLevel" type="xs:integer" />
    <xs:element name="Discontinued" type="xs:boolean" />
    </xs:schema> '
     
    E. retrieve Schemas
    SELECT * FROM sys.xml_schema_collections
    Schemas are stored in Sys.xml_schema_collections
    11:00 PM | Add a comment | Read comments (1) | Permalink | Blog it