User Tools

Site Tools


notes:sql:xml

XML and SQL Server

Read an XML string:

-- Obtain an XML string
DECLARE @XML VARCHAR(1000)
SET @XML = '<books>' +
	'<book><title>The C++ Programming Language</title><author>Bjarne Stroustrup</author></book>' +
	'<book><title>Effective C++</title><author>Scott Meyers</author></book>' +
	'</books>'
 
-- Create a table to insert book titles
CREATE TABLE #titles (BookTitle NVARCHAR(250))
 
-- Declare a document handler
DECLARE @hDoc INT
 
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
 
-- Read XML text from the XML document
INSERT INTO #titles(BookTitle)
SELECT title
FROM OPENXML (@hDoc, '/books/book', 2) -- flag=2 means element-centric mapping
WITH (title NVARCHAR(250))
 
-- Show results
SELECT * FROM #titles
 
-- Clean-up
EXEC sp_xml_removedocument @hDoc
DROP TABLE #titles
 
-- Results:
BookTitle
----------------------------
The C++ Programming LANGUAGE
Effective C++

Another example of SELECT * FROM OPENXML:

DECLARE @hDoc INT, @xml VARCHAR(8000)
 
SET @xml =
'<Lottery>
   <Customers>
     <FirstName>Marina</FirstName>
     <LastName>Zupen</LastName>
     <Orders>
       <DateEntry>9/18/2001 7:3</DateEntry>
       <OrderNo>1500</OrderNo>
       <Tickets>
         <HolderFirstName>Marina</HolderFirstName>
         <HolderLastName>Zupen</HolderLastName>
       </Tickets>
     </Orders>
   </Customers>
   <Customers>
     <FirstName>Thomas</FirstName>
     <LastName>Atkins</LastName>
     <Orders>
       <DateEntry>9/18/2001 8:29</DateEntry>
       <OrderNo>1501</OrderNo>
       <Tickets>
         <HolderFirstName>Tom</HolderFirstName>
         <HolderLastName>Alton</HolderLastName>
       </Tickets>
       <Tickets>
         <HolderFirstName>Susan</HolderFirstName>
         <HolderLastName>Alton</HolderLastName>
       </Tickets>
    </Orders>
  </Customers>
</Lottery>'
 
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml
 
SELECT * FROM OPENXML(@hDoc, '/Lottery/Customers', 2)    
WITH (FirstName VARCHAR(30),
      LastName VARCHAR(30))
 
SELECT * FROM OPENXML(@hDoc, '/Lottery/Customers/Orders', 2) 
WITH (OrderNo VARCHAR(5))
 
SELECT * FROM OPENXML(@hDoc, '/Lottery/Customers/Orders/Tickets', 2)    
WITH (HolderFirstName VARCHAR(30),
      HolderLastName VARCHAR(30))
 
EXEC sp_xml_removedocument @hDoc
FirstName                      LastName
------------------------------ ------------------------------
Marina                         Zupen
Thomas                         Atkins
 
(2 row(s) affected)
 
OrderNo
-------
1500
1501
 
(2 row(s) affected)
 
HolderFirstName                HolderLastName
------------------------------ ------------------------------
Marina                         Zupen
Tom                            Alton
Susan                          Alton
 
(3 row(s) affected)

Read an XML document line-by-line:

-- Create a table to insert XML nodes
CREATE TABLE #nodes (XmlNode NVARCHAR(1000))
 
-- Read an XML document located at C:\Temp on the server
BULK INSERT #nodes FROM 'C:\Temp\books.xml'
 
SELECT * FROM #nodes
 
DROP TABLE #nodes
 
-- Results:
XmlNode
---------------------------------------------------
<books>
    <book>
        <title>The C++ Programming Language</title>
        <author>Bjarne Stroustrup</author>
    </book>
    <book>
        <title>Effective C++</title>
        <author>Scott Meyers</author>
    </book>
</books>
 
(10 ROW(s) affected)
notes/sql/xml.txt · Last modified: 2015/06/24 by admin