Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to loop to read xml column from the table?

Author  Topic 

Vaishu
Posting Yak Master

178 Posts
Posted - 2008-08-12 : 06:02:31
HI

I am using SQL 2005 standard edition.

I have a table with column as below
OrderId(Primary Key), grossamt,statusId,BilltoAddress(nvarchar 1500)

Column "BillToAddress" have address detail in the form of xml

The below code (posted by visak16 in this forum)to loop through each row in the table.I am using this to read address from xml fields which is in "BillToAddress" (nvarchar1500). But it brings only the first record(row)

My question: How to loop through all row in a table to read xml fields and how do I select other columns(grossamt,statusId)along with the result from "BillToAddress" from the table

quote:

DECLARE @PK int,@xmlcol nvarchar(1500)
SELECT @PK=MIN(OrderId)
FROm Store_Order

WHILE @PK IS NOT NULL
BEGIN
SELECT @xmlcol=(BillToAddress)
FROm Store_Order
WHERE OrderId>@PK

EXEC sp_xml_preparedocument @PK OUTPUT, @xmlcol
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@PK, '/Address',2)
WITH (UserName nvarchar(50),
FirstName nvarchar(50),
LastName nvarchar(50),
Phone nvarchar(50),
Email nvarchar(50),
Address1 nvarchar(150),
Address2 nvarchar (150),
City nvarchar (50),
StateOrRegion nvarchar(150),
PostalCode nvarchar(50),
Country nvarchar(50),
AddressType nvarchar(50))

SELECT @PK=MIN(OrderId)
FROm Store_Order
WHERE OrderId>@PK
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-08-12 : 06:09:38
the posted code is not fully what i suggested. cahnge like this and see.

DECLARE @PK int,@xmlcol nvarchar(1500),@docHandle int
SELECT @PK=MIN(OrderId)
FROm Store_Order

WHILE @PK IS NOT NULL
BEGIN
SELECT @xmlcol=(BillToAddress)
FROm Store_Order
WHERE OrderId>=@PK

EXEC sp_xml_preparedocument @PK ,@docHandle OUTPUT, @xmlcol
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/Address',2)
WITH (UserName nvarchar(50),
FirstName nvarchar(50),
LastName nvarchar(50), 
Phone nvarchar(50),
Email nvarchar(50),
Address1 nvarchar(150),
Address2 nvarchar (150),
City nvarchar (50),
StateOrRegion nvarchar(150),
PostalCode nvarchar(50),
Country nvarchar(50),
AddressType nvarchar(50))

SELECT @PK=MIN(OrderId)
FROm Store_Order
WHERE OrderId>@PK
END
Go to Top of Page

Vaishu
Posting Yak Master

178 Posts
Posted - 2008-08-12 : 08:18:00
Hi Visahk

It works but with the following issue evenhough I have dual core processor and 3Gb of Ram. Also results are excuted in seperate result window for each row.

XML document could not be created because server memory is low. Use sp_xml_removedocument to release XML documents.

So I have decided to create trigger to read xml and to insert or update to other table as below.
quote:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER TRIGGER [OrderMan]
ON [dbo].[Store_Order]
AFTER INSERT,UPDATE
AS

BEGIN

SET NOCOUNT ON;

DECLARE @idoc int
DECLARE @doc nvarchar(1500)
SET @doc = (select BillToAddress from inserted)

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.

Insert OrderManager (UserName,FirstName,LastName,Phone,
Email,Address1,Address2,City,StateOrRegion,
PostalCode,Country,AddressType
)
SELECT *
FROM OPENXML (@idoc, '/Address',2)
WITH (UserName nvarchar(50),FirstName nvarchar(50),
LastName nvarchar(50),Phone nvarchar(50),
Email nvarchar(50),Address1 nvarchar(150),
Address2 nvarchar (150),City nvarchar (50),
StateOrRegion nvarchar(150),PostalCode nvarchar(50),
Country nvarchar(50),AddressType nvarchar(50))


END


My question is How do I pick another column form Stroe_order to insert to OrderMan table
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-08-12 : 08:28:46
You need to remove document each time after use. use sp_xml_removedocument to remove it just before END
EXEC sp_xml_removedocument @idoc
Go to Top of Page

Vaishu
Posting Yak Master

178 Posts
Posted - 2008-08-12 : 09:12:08

Hi

But How do I pick "grossamt","statusId" from Store_order to insert into OrderManager in seingle trigger.

quote:
Originally posted by visakh16

You need to remove document each time after use. use sp_xml_removedocument to remove it just before END
EXEC sp_xml_removedocument @idoc
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-08-12 : 09:45:07
quote:
Originally posted by Vaishu


Hi

But How do I pick "grossamt","statusId" from Store_order to insert into OrderManager in seingle trigger.

quote:
Originally posted by visakh16

You need to remove document each time after use. use sp_xml_removedocument to remove it just before END
EXEC sp_xml_removedocument @idoc


trigger? which trigger?
Go to Top of Page

Vaishu
Posting Yak Master

178 Posts
Posted - 2008-08-12 : 10:25:29
Hi

(1) I have table (Store_Order) with columns(OrderId,OrderstatusId,GrossAmount,
BilltoAddress(nvarchar(1500) - This column has adress stored in xml format)

(2) I have created another table(OrderManager) with same columns as Store_Order and additional columns as name,firstname,lastname and so on. So I can insert the data queried from column "BillToAddress" of the Store_Order

(3) Created trigger on Store_Order to read xml fields from column "BillToAddress" of Store_Order and insert into table Ordermanager as below (THIS trigger only query "BillToAddress" column of Store_Order to read the xml data and insert into Ordermanager. I don't know how to query other columns of Store_order while query the column "BillToAddress")

quote:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER TRIGGER [OrderMan]
ON [dbo].[Store_Order]
AFTER INSERT,UPDATE
AS

BEGIN

SET NOCOUNT ON;

DECLARE @idoc int
DECLARE @doc nvarchar(1500)
SET @doc = (select BillToAddress from inserted)

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.

Insert OrderManager (UserName,FirstName,LastName,Phone,
Email,Address1,Address2,City,StateOrRegion,
PostalCode,Country,AddressType
)
SELECT *
FROM OPENXML (@idoc, '/Address',2)
WITH (UserName nvarchar(50),FirstName nvarchar(50),
LastName nvarchar(50),Phone nvarchar(50),
Email nvarchar(50),Address1 nvarchar(150),
Address2 nvarchar (150),City nvarchar (50),
StateOrRegion nvarchar(150),PostalCode nvarchar(50),
Country nvarchar(50),AddressType nvarchar(50))

END


(4) This triger works fine to query only the "BillToAddress" column.

(5) Using the above triger I am able to query only one column(BillToAddress) of the table (Stroe_Order). So How do I query other columns in Store_order along with coloumn (BillToAddress) and Insert into another table OrderManager using the above triger (need to modify).
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-08-12 : 13:06:58
You're assuming inserted will have only one record but this will not be the case always. it should be something like this:-

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER TRIGGER [OrderMan]
ON [dbo].[Store_Order]
AFTER INSERT,UPDATE
AS 

BEGIN

SET NOCOUNT ON;

INSERT INTO OrderManager (OrderId,OrderstatusId,GrossAmount)
SELECT OrderId,OrderstatusId,GrossAmount
FROM INSERTED


DECLARE @idoc int,@PK int
DECLARE @doc nvarchar(1500)
SELECT @PK=MIN(OrderId)
FROM INSERTED

WHILE @PK IS NOT NULL
BEGIN
SET @doc = (select BillToAddress from inserted where OrderId=@PK)

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.

Update  o
SET o.UserName=t.UserName,
o.FirstName=t.FirstName,
o.LastName=t.LastName,
o.Phone=t.Phone,
o.Email=t.Email,
o.Address1=t.Address1,
o.Address2=t.Address2,
o.City=t.City,
o.StateOrRegion=t.StateOrRegion,
o.PostalCode=t.PostalCode,
o.Country=t.Country,
o.AddressType=t.AddressType
FROM  OrderManager o
CROSS JOIN
(
SELECT *
FROM OPENXML (@idoc, '/Address',2)
WITH (UserName nvarchar(50),FirstName nvarchar(50),
LastName nvarchar(50),Phone nvarchar(50),
Email nvarchar(50),Address1 nvarchar(150),
Address2 nvarchar (150),City nvarchar (50),
StateOrRegion nvarchar(150),PostalCode nvarchar(50),
Country nvarchar(50),AddressType nvarchar(50))
)t
WHERE o.OrderId=@PK


SELECT @PK=MIN(OrderId)
FROM INSERTED
WHERE OrderId>@PK

EXEC sp_xml_removedocument @idoc
END
END
Go to Top of Page

Vaishu
Posting Yak Master

178 Posts
Posted - 2008-08-13 : 04:09:58

HI

Thanks a lot. It works and fantastic

quote:
Originally posted by visakh16

You're assuming inserted will have only one record but this will not be the case always. it should be something like this:-

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER TRIGGER [OrderMan]
ON [dbo].[Store_Order]
AFTER INSERT,UPDATE
AS 

BEGIN

SET NOCOUNT ON;

INSERT INTO OrderManager (OrderId,OrderstatusId,GrossAmount)
SELECT OrderId,OrderstatusId,GrossAmount
FROM INSERTED


DECLARE @idoc int,@PK int
DECLARE @doc nvarchar(1500)
SELECT @PK=MIN(OrderId)
FROM INSERTED

WHILE @PK IS NOT NULL
BEGIN
SET @doc = (select BillToAddress from inserted where OrderId=@PK)

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.

Update  o
SET o.UserName=t.UserName,
o.FirstName=t.FirstName,
o.LastName=t.LastName,
o.Phone=t.Phone,
o.Email=t.Email,
o.Address1=t.Address1,
o.Address2=t.Address2,
o.City=t.City,
o.StateOrRegion=t.StateOrRegion,
o.PostalCode=t.PostalCode,
o.Country=t.Country,
o.AddressType=t.AddressType
FROM  OrderManager o
CROSS JOIN
(
SELECT *
FROM OPENXML (@idoc, '/Address',2)
WITH (UserName nvarchar(50),FirstName nvarchar(50),
LastName nvarchar(50),Phone nvarchar(50),
Email nvarchar(50),Address1 nvarchar(150),
Address2 nvarchar (150),City nvarchar (50),
StateOrRegion nvarchar(150),PostalCode nvarchar(50),
Country nvarchar(50),AddressType nvarchar(50))
)t
WHERE o.OrderId=@PK


SELECT @PK=MIN(OrderId)
FROM INSERTED
WHERE OrderId>@PK

EXEC sp_xml_removedocument @idoc
END
END
Go to Top of Page

neophyte226
Starting Member

7 Posts
Posted - 2009-08-03 : 14:50:23
What am I missing in order to return multiple values for a column off of the record number?

DECLARE @h int
DECLARE @x xml
set @x = (select cast('<test>
<date>10-02-2006</date>
<rec-number>9</rec-number>
<tranDetail>
<name>Art</name>
<name>John</name>
<name>Todd</name>
</tranDetail>
</test>
' as xml))
EXEC sp_xml_preparedocument @h output, @x

select * from openxml(@h, 'test', 2)
with (date varchar(20) 'date',
[rec-number] int 'rec-number',
[name] varchar(10) 'tranDetail/name'
)
EXEC sp_xml_removedocument @h

returns
date rec-number name
10-02-2006 9 Art

but i'm looking for
date rec-number name
10-02-2006 9 Art
10-02-2006 9 John
10-02-2006 9 Todd
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-08-03 : 15:40:12
Don't hijack other topics.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=130555

N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site