sp_xml_preparedocument and OPENXML -- sql server programming

Dan Guzman
5/6/2004 11:15:28 PM

[quoted text, click to view]

The first example below was gleaned from the Books Online. I added
processing instruction <?xml version="1.0" encoding="UTF-8"?> and change the
ROOT element to 'Customers'. Please post your code and sample XML document
if you still need help.

DECLARE @hdoc int
DECLARE @doc varchar(1000)
SET @doc ='
<?xml version="1.0" encoding="UTF-8"?>
<Customers>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</Customers>'

EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
SELECT *
FROM OPENXML (@hdoc, '/Customers/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))

EXEC sp_xml_removedocument @hdoc
GO

[quoted text, click to view]

The VBScript example below loads XML text from a file into a table: It uses
the XML DOM to ensure the document is well-formed.

--sample table
CREATE TABLE dbo.MyTable
(
FileName nvarchar(255) NOT NULL,
MyXml ntext
)

'VBScript example
fileName = "C:\MyXmlFiles\MyXmlFile.xml"
Set myxmlDocument = CreateObject("Msxml2.DomDocument.4.0")
Set myConnection = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command")

myConnection.Open "Provider=SQLOLEDB;" & _
"Data Source=DBDELTA1;" & _
"Integrated Security=SSPI;" & _
"Initial Catalog=MyDatabase"

Set myxmlDocument = CreateObject("Msxml2.DomDocument.4.0")
myxmlDocument.Load(fileName)

myCommand.CommandText = "INSERT INTO MyTable VALUES(?, ?)"

myCommand.ActiveConnection = myConnection
Set myFileNameParameter = myCommand.CreateParameter( _
"@MyFileNameParameter", 203, 1, 255, fileName)
myCommand.Parameters.Append myFileNameParameter

Set myXmlParameter = myCommand.CreateParameter( _
"@MyXmlParameter", 203, 1, 1000000, myxmlDocument.Xml)
myCommand.Parameters.Append myXmlParameter
myXmlParameter.Value = myxmlDocument.Xml

myCommand.Execute

--
Hope this helps.

Dan Guzman
SQL Server MVP

[quoted text, click to view]

Steve Kass
5/7/2004 12:13:34 AM

Rob,

It would be a big help if you provided some details. If you run the
following script in Query Analyzer, you'll see that the "stuff at the
top" does not break sp_xml_preparedocument and OPENXML. At least when I
run it, I get this result:

CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzalez

Do you get the same result? If so, are you trying the T-SQL part of
your code in Query Analyzer, so you can be sure the problem isn't before
things get to the server?

-- run the script below

DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT,
'<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<!DOCTYPE MUCKYMUCK SYSTEM "http://whatever.wherever.topleveldomain">

<BLM31604>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzalez">
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>

</BLM31604>
'

SELECT *
FROM OPENXML (@idoc, '/BLM31604/Customer', 1)
WITH (CustomerID varchar(10),
ContactName varchar(20)
)

EXEC sp_xml_removedocument @idoc

-- Steve Kass
-- Drew University
-- Ref: B83617DF-30B6-48FE-A868-E9EE5CE3E78C

[quoted text, click to view]

rob
5/7/2004 6:45:47 AM

Thanks for responding...

I see that the file's contents are now in a table, but all in one column.
How does one go about placing the data into individual columns ?

[quoted text, click to view]

Dan Guzman
5/7/2004 8:58:44 AM

Check out the link below for SQLXML. This contains utilities to facilitate
transforming XML data into relational data.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/anch_SQLXML.asp

--
Hope this helps.

Dan Guzman
SQL Server MVP

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : sp_xml_preparedocument and OPENXML