can openxml write multiple fields - 1 row?
From: Ed (anonymous_at_discussions.microsoft.com)
Date: 08/05/04
- Next message: jf pedrazzani: "Re: FOR XML AUTO returning blank."
- Previous message: David Sampson: "Re: XMLHTTP.Send returns the same string"
- In reply to: Ed: "can openxml write multiple fields - 1 row?"
- Next in thread: ED: "can openxml write multiple fields - 1 row?"
- Reply: ED: "can openxml write multiple fields - 1 row?"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 4 Aug 2004 22:26:37 -0700
Well, I checked out some examples on Books On Line. This
definitely shed some light on my question. I might be
able to figure something out from the examples. My only
thing is this example is using sub attributes. Is it
possible to get it all in one element/attribute?
---------------------------------------------------------
Example B.
B. Specify ColPattern for mapping between columns and the
XML attributes
This query returns customer ID, order date, product ID and
quantity attributes from the XML document. The rowpattern
identifies the <OrderDetails> elements. ProductID and
Quantity are the attributes of the <OrderDetails> element.
However, the OrderID, CustomerID and OrderDate are the
attributes of the parent element (<Orders>).
The optional ColPattern is specified, indicating that:
The OrderID, CustomerID and OrderDate in the rowset map to
the attributes of the parent of the nodes identified by
rowpattern in the XML document.
The ProdID column in the rowset maps to the ProductID
attribute, and the Qty column in the rowset maps to the
Quantity attribute of the nodes identified in rowpattern.
Although the element-centric mapping is specified by the
flags parameter, the mapping specified in ColPattern
overwrites this mapping.
declare @idoc int
declare @doc varchar(1000)
set @doc ='
<ROOT>
<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 Gonzlez">
<Order OrderID="10283" CustomerID="LILAS"
EmployeeID="3"
OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
exec sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML
(@idoc, '/ROOT/Customer/Order/OrderDetail',2)
WITH (OrderID int '../@OrderID',
CustomerID varchar(10) '../@CustomerID',
OrderDate datetime '../@OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity')
This is the result:
OrderID CustomerID OrderDate
ProdID Qty
-----------------------------------------------------------
-------------
10248 VINET 1996-07-04 00:00:00.000 11 12
10248 VINET 1996-07-04 00:00:00.000 42 10
10283 LILAS 1996-08-16 00:00:00.000 72 3
***********************************************************
***********************************************************
Thanks,
Ed
>-----Original Message-----
>Hello,
>
>I got an xml routine from the Sql Server NG where I can
>pass a string of data (comma delimited) from an outside
>source (like a VB app) to a sql server sp, convert the
>data to an xml doc, and then use openxml to write this
>data to a table. This routine only generates one int
>field and generates as many rows as there are comma
>delimiters in the string. I would like to know if it is
>possible to create the xml doc such that it produces one
>row of data with multiple fields (multiple datatypes).
>Here is an sp that takes a string arg, uses the
xmlconvert
>UDF and then calls openxml:
>
>Say I pass in "1,2,3"
>exec stp_xmlTest "1,2,3"
>-----------------------------------------------
>CREATE PROCEDURE [stp_xmlTest]
> @xml varchar(8000)
>AS
>SET NOCOUNT ON
>Declare @iDoc Int
>Set @xml=dbo.ConvertToXML(@xml, ',')
>EXEC sp_xml_preparedocument @iDoc output, @xml
>SELECT Value
>INTO tmp
>FROM OPENXML(@iDoc, '/ROOT/Worktable', 1)
> WITH ([Value] INT '@Value')
>EXEC sp_xml_removedocument @iDoc
>GO
>----------------------------------------------------
>----------------------------------------------------
>CREATE FUNCTION dbo.ConvertToXML( @InputStr varchar
>(8000), @Delim varchar (5))
>returns varchar (8000)
>as
>begin
>return ('<ROOT><Worktable Value="'
> + replace (@InputStr, @Delim, '"/><Worktable Value="')
> + '"/></ROOT>')
>end
>------------------------------------------------------
>
>this yields
>1
>2
>3
>
>Instead of Select Into tmp, I would like to Insert Into
>tbl1, values(int, varchar, varchar, date, int)
>
>where tbl1 contains the fields (rownum, fName, lName,
>bDate, Age). How could I modify the convert function and
>sp above to accomplish this? Actually, I think I can
>answer part of this: I would declare a variable for each
>field
>
>Declare @rownum int,
>declare @fName varchar(50),
>declare @lName varchar(50),
>declare @DOB datetime,
>declare @Age int
>
>So how would I modify my ConvertToXML UDF and the call to
>openxml so that I Insert this data into one row? How do
I
>parse the string in ConvertToXML to get only one row?
And
>in the openxml function
>
>FROM OPENXML(@rownum, @fName, @lName, @DOB,
>@Age, '/ROOT/Worktable', 1)
> WITH ([Value] INT '@Value')
>
>Let's see, I am guessing that I could have @value1,
>@value2, @value3, @value4, @value5
>
>Insert Into tbl1(value1, value2, value3, value4, value5)
>Select -- am I close here?
>FROM OPENXML(@rownum, @fName, @lName, @DOB,
>@Age, '/ROOT/Worktable', 1)
> WITH ([value1] INT '@value1', [value2] varchar
>(50) '@value2', [value3] varchar(50), '@value3', [value4]
>datetime '@value4', [value5] INT '@value5')
>
>And in the ConvertToXML function
>
>return ('<ROOT><Worktable Value="'
> + replace (@InputStr, @Delim, '"/><Worktable Value="')
> + '"/></ROOT>')
>
>would be
>
>return ('<ROOT><Worktable value1="'
> + replace (@InputStr, @Delim, '"/><Worktable value1="')
> + '"/></ROOT>')
>
>--I'm lost now, how do I parse the InputStr to get
value1,
>value2, ... value5 all on one row? How about the sp?
can
>I do an Insert Into tbl1(...) Select From OpenXML...?
>
>Thanks,
>Ed
>
>
>.
>
- Next message: jf pedrazzani: "Re: FOR XML AUTO returning blank."
- Previous message: David Sampson: "Re: XMLHTTP.Send returns the same string"
- In reply to: Ed: "can openxml write multiple fields - 1 row?"
- Next in thread: ED: "can openxml write multiple fields - 1 row?"
- Reply: ED: "can openxml write multiple fields - 1 row?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
