help parsing xml with TSQL | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

help parsing xml with TSQL

I was just assigned a project at a new job. I am supposed to parse XML using TSQL and I have never done anything with XML in TSQL. Can someone help me parse this XML? I can parse out everything except all of the specific stores. I only get the first store in each request. See code below….. declare @XML varchar(8000) SET @XML = ‘ <ROOT> <Request> <RequestID>3</RequestID> <AllStores>true</AllStores> <StoreList> <Stores> <Store>4</Store> <Store>16</Store> <Store>451</Store> </Stores> </StoreList> <Sku>281537</Sku> <BeginDate>2007-01-01</BeginDate> <EndDate>2007-06-01</EndDate> <IsReserveSku>true</IsReserveSku> </Request> <Request> <RequestID>6</RequestID> <AllStores>false</AllStores> <StoreList> <Stores> <Store>9</Store> <Store>16</Store> <Store>451</Store> </Stores> </StoreList> <Sku>999999</Sku> <BeginDate>2007-02-01</BeginDate> <EndDate>2007-08-01</EndDate> <IsReserveSku>false</IsReserveSku> </Request> <Request> <RequestID>8</RequestID> <AllStores>true</AllStores> <StoreList> <Stores> <Store>4</Store> <Store>16</Store> <Store>451</Store> </Stores> </StoreList> <Sku>281537</Sku> <BeginDate>2007-03-01</BeginDate> <EndDate>2007-06-01</EndDate> <IsReserveSku>true</IsReserveSku> </Request> </ROOT> ‘ –select len(@xml) DECLARE @hdoc int, @rqstid int, @allstore char(5), @store int, @sku int, @begindate smalldatetime, @enddate smalldatetime, @IsReserveSku char(5)
EXEC sp_xml_preparedocument @hdoc OUTPUT, @XML declare @stores table (RequestID int, AllStores char(5),Stores int, Sku int,BeginDate datetime, EndDate datetime, IsReserveSku char(5)) insert into @stores (RequestID, AllStores, Stores, Sku, BeginDate, EndDate, IsReserveSku) SELECT RequestID, AllStores, Stores, Sku, BeginDate, EndDate, IsReserveSku FROM –OPENXML (@hdoc, ‘ROOT/Request’,1) OPENXML (@hdoc, ‘ROOT/Request’,1) WITH (RequestID int ‘RequestID/text()’, AllStores char(5) ‘AllStores/text()’, Stores int ‘StoreList/Stores/Store/text()’, –only gets first store Sku int ‘Sku/text()’, BeginDate datetime ‘BeginDate/text()’, EndDate datetime ‘EndDate/text()’, IsReserveSku char(5) ‘IsReserveSku/text()’ ) select * from @stores
Andy

Check this site if it helps for OpenXML:
http://www.sqlservercentral.com/columnists/rVasant/usingopenxml.asp Thanks, Name
———
Dilli Grg (1 row(s) affected)
DilliGrg, Thank you for the link. Based on it, I changed my bottom query to what is below. I now get the correct number of records, but they are all null. Any ideas? declare @stores table (RequestID int, AllStoreschar(5),Stores int, Sku int,BeginDate datetime, EndDate datetime, IsReserveSku char(5))
insert into @stores (RequestID, AllStores, Stores, Sku, BeginDate, EndDate, IsReserveSku)
SELECT
RequestID, AllStores, Stores, Sku, BeginDate, EndDate, IsReserveSku
FROM
–OPENXML (@hdoc, ‘ROOT/Request’,1)
OPENXML (@hdoc, ‘ROOT/Request/StoreList/Stores/Store’,2)
WITH
(RequestIDint’@RequestID’,
AllStoreschar(5)’@AllStores’,
Storesint’@Store’,
Skuint’@Sku’,
BeginDatedatetime’@BeginDate’,
EndDatedatetime’@EndDate’,
IsReserveSku char(5)’@IsReserveSku’
)
Thanks for your help. You have no idea how much I appreciate it.
Andy
Why you are declaring as Varchar(8000)? You can declare as xml datatype if you are using 2005…
Before using the XML procedures… read the following from BOL… A parsed document is stored in the internal cache of SQL Server 2005. The MSXML parser uses one-eighth the total memory available for SQL Server. To avoid running out of memory, run sp_xml_removedocument to free up the memory. Check the following may help you…
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=20598
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

MohammedU, I checked out the link you sent but I’m still having problems. I changed the varchar(8000) to XML so the .nodes works. I do have the sp_xml_removedocument at the bottom I just didn’t put it in my question. Can you tell me what I’m doing wrong in the first query? The query below returns the correct number of rows, but they’re all NULL. Select T.c.value(‘RequestID[1]’,’int’) As ‘RequestID’,
T.c.value(‘AllStores[1]’,’char(5)’) As ‘AllStores’,
T.c.value(‘Store[1]’,’int’) As ‘Stores’,
T.c.value(‘Sku[1]’,’int’) As ‘Sku’,
T.c.value(‘BeginDate[1]’,’datetime’) As ‘BeginDate’,
T.c.value(‘EndDate[1]’,’datetime’) As ‘EndDate’,
T.c.value(‘IsReserveSku[1]’,’char(5)’) As ‘IsReserveSku’
From @XML.nodes(‘/ROOT/Request/StoreList/Stores/Store’) T(c) The query below returns only the 3 top rows for each part of the XML and all of the store
information is NULL.
Select T.c.value(‘RequestID[1]’,’int’) As ‘RequestID’,
T.c.value(‘AllStores[1]’,’char(5)’) As ‘AllStores’,
T.c.value(‘Store[1]’,’int’) As ‘Stores’,
T.c.value(‘Sku[1]’,’int’) As ‘Sku’,
T.c.value(‘BeginDate[1]’,’datetime’) As ‘BeginDate’,
T.c.value(‘EndDate[1]’,’datetime’) As ‘EndDate’,
T.c.value(‘IsReserveSku[1]’,’char(5)’) As ‘IsReserveSku’
From @XML.nodes(‘/ROOT/Request’) T(c)
Andy
We finally figured out the answer. I pasted it below. SELECT
RequestID, AllStores, Stores, Sku, BeginDate, EndDate, IsReserveSku
FROM
OPENXML (@hdoc, ‘ROOT/Request/StoreList/Stores/Store’,1)
WITH
(RequestID int ‘../../../RequestID’,
AllStores char(5) ‘../../../AllStores’,
Stores int ‘text()’,
Sku int ‘../../../Sku’,
BeginDate datetime ‘../../../BeginDate’,
EndDate datetime ‘../../../EndDate’,
IsReserveSku char(5) ‘../../../IsReserveSku’
) Andy
]]>