MS SQL XML - XML - Am I missing something?

Author

XML - Am I missing something?

MetalSQL

2006-11-08, 7:14 pm

In many textbooks, including BOL, every OPENXML example I have ever seen
looks something like this (This one's straight from BOL - I removed the
leading "<" symbol on each line, so the code will show up in the post):

declare @idoc int
declare @doc varchar(1000)
set @doc ="
ROOT>
Customers CustomerID="VINET" ContactName="Paul Henriot">
Orders CustomerID="VINET" EmployeeID="5" OrderDate=
"1996-07-04T00:00:00">
Order_x0020_Details OrderID="10248" ProductID="11" Quantity="12"/>
Order_x0020_Details OrderID="10248" ProductID="42" Quantity="10"/>
/Orders>
/Customers>
Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
Orders CustomerID="LILAS" EmployeeID="3" OrderDate=
"1996-08-16T00:00:00">
Order_x0020_Details OrderID="10283" ProductID="72" Quantity="3"/>
/Orders>
/Customers>
/ROOT>"
--Create an internal representation of the XML document.
exec sp_xml_preparedocume
nt @idoc OUTPUT, @doc
-- SELECT statement using OPENXML rowset provider
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customers')
EXEC sp_xml_removedocumen
t @idoc

The part I'm not understanding is the "SET @doc =" statement. In every
example I've ever seen, from BOL and 4 textbooks, shows the XML document
hard-coded into the OPENXML prodecure!

How do you pass the text from some "mydata.xml" document file, stored in the
Windows 2003 Server file system somewhere, to the SET staement in the OPENXML
procedure?

Thanks!

Kent Tegels

2006-11-09, 12:13 am

Hello MetalSQL,

declare @idoc int
declare @doc xml

select @doc = bulkcolumn from
openrowset(bulk 'c:\postalcodes.xml',single_blob) as p

exec sp_xml_preparedocume
nt @idoc OUTPUT, @doc

SELECT *
FROM OPENXML (@idoc, '//zipCode')
EXEC sp_xml_removedocumen
t @idoc

Thanks,
Kent Tegels
http://staff.develop.com/ktegels/

MetalSQL

2006-11-14, 7:18 pm

Thanks, Ken, this is mostly helpful, except I forgot to specify a very
important detail...

How do you do it in SQL Server 2000? That's the version my company uses.

Thanks!

Kent Tegels

2006-11-14, 7:18 pm

Hello MetalSQL,

M> How do you do it in SQL Server 2000? That's the version my company
M> uses.

I don't, this bulk feature isn't supported in 2000.

Thanks,
Kent Tegels
http://staff.develop.com/ktegels/

Joe Fawcett

2006-12-06, 7:16 pm

"MetalSQL" < MetalSQL@discussions
.microsoft.com> wrote in message
news:6FC71A95-2905-4A39-8808- 44A310437F71@microso
ft.com...
> Thanks, Ken, this is mostly helpful, except I forgot to specify a very
> important detail...
>
> How do you do it in SQL Server 2000? That's the version my company uses.
>
> Thanks!

her's a snippet of code we use to pass a shopping basket to a stored
procedure and calculate the shipping cost. In the example the XMl is hard
codes but in real life it is passed as NVARCHAR string.

DECLARE @BasketXml NVARCHAR(4000)
SET @BasketXml =
N'

'
DECLARE @BasketXmlDoc INT
EXEC sp_xml_preparedocume
nt @BasketXmlDoc OUTPUT, @BasketXml
DECLARE @BasicBasket TABLE
(
ProductId NVARCHAR(20),
Quantity INT
)
INSERT
@BasicBasket
SELECT
p ProductId,
q Quantity
FROM OPENXML (@BasketXmlDoc, '/b/i', 1)
WITH
(p NVARCHAR(20),
q INT)
EXEC sp_xml_removedocumen
t @BasketXmlDoc
SELECT * FROM @BasicBasket

Of course, you need to load the file's XML data somehow. You can do this
inside a stored proceure with automation or client-side.
--

Joe Fawcett (MVP - XML)

http://joe.fawcett.name

MetalSQL

2006-12-06, 7:16 pm

Joe,
I appreciate your reply, but your response left out the one exact
detail that the original question was asking for.

In your reply, you state, "In the example the XML is hard codes but in
real life it is passed as NVARCHAR string."

That's the part I'm trying to figure out. The rest of the example you
gave, much like all the other examples I've seen, shows the XML that is set
to @BasketXml hard-coded into the OPENXML statement. The rest is
straight-forward OPENXML syntax, which I already get.

How do you get the information from some "filename.xml" file loaded
into the @BasketXml NVARCHAR string in SQL Server 2000? It's that "real life"
part you refer to that I'm looking for.

Thanks for your help!

"Joe Fawcett" wrote:

> "MetalSQL" < MetalSQL@discussions
.microsoft.com> wrote in message
> news:6FC71A95-2905-4A39-8808- 44A310437F71@microso
ft.com...
>
> her's a snippet of code we use to pass a shopping basket to a stored
> procedure and calculate the shipping cost. In the example the XMl is hard
> codes but in real life it is passed as NVARCHAR string.
>
> DECLARE @BasketXml NVARCHAR(4000)
> SET @BasketXml =
> N'
> 
> '
> DECLARE @BasketXmlDoc INT
> EXEC sp_xml_preparedocume
nt @BasketXmlDoc OUTPUT, @BasketXml
> DECLARE @BasicBasket TABLE
> (
> ProductId NVARCHAR(20),
> Quantity INT
> )
> INSERT
> @BasicBasket
> SELECT
> p ProductId,
> q Quantity
> FROM OPENXML (@BasketXmlDoc, '/b/i', 1)
> WITH
> (p NVARCHAR(20),
> q INT)
> EXEC sp_xml_removedocumen
t @BasketXmlDoc
> SELECT * FROM @BasicBasket
>
> Of course, you need to load the file's XML data somehow. You can do this
> inside a stored proceure with automation or client-side.
> --
>
> Joe Fawcett (MVP - XML)
>
> http://joe.fawcett.name
>
>
>

Joe Fawcett

2006-12-06, 7:16 pm

"MetalSQL" < MetalSQL@discussions
.microsoft.com> wrote in message
news:7A620057-64F1-43C0-9F77- 36A5127822FB@microso
ft.com...[color=darkred]
> Joe,
> I appreciate your reply, but your response left out the one exact
> detail that the original question was asking for.
>
> In your reply, you state, "In the example the XML is hard codes but
> in
> real life it is passed as NVARCHAR string."
>
> That's the part I'm trying to figure out. The rest of the example you
> gave, much like all the other examples I've seen, shows the XML that is
> set
> to @BasketXml hard-coded into the OPENXML statement. The rest is
> straight-forward OPENXML syntax, which I already get.
>
> How do you get the information from some "filename.xml" file loaded
> into the @BasketXml NVARCHAR string in SQL Server 2000? It's that "real
> life"
> part you refer to that I'm looking for.
>
>
> Thanks for your help!
>
>
> "Joe Fawcett" wrote:
>
Okay, you have a number of choices:
If you want to open the file from within the stored procedure then you can
automate the FileSystemObject using the OA_ group of stored procs which can
create and utilise COM.

If you want to do it outside the stored procedure you can read the XML using
FileSystemObject or XmlDocument or MSXML and pass the string from those.

If that's not detailed enough just give a description of your current
workflow and what's running where etc.

--

Joe Fawcett (MVP - XML)

http://joe.fawcett.name

Sponsored Links