June 27, 2012 at 5:01 pm
Hello I am new with XML. Right now I am trying to select from 2 tables I am getting the results to XML, but not in the order I would like any help would be appreciated. if the header table has 2 rows and each header row has 1 detail record it is currently coming out like.
Current output:
header
header
LineItem
LineItem
what I am trying to accomplish
header
LineItem
header
LineItem
Let me know if you the actual code would help.
Example Code:
CREATE TABLE #tmp_Header ( ID INT, XMLDocument XML )
INSERT INTO #tmp_Header
( ID )
VALUES ( 1 )
UPDATE #tmp_Header
SET XMLDocument =
( SELECT
( SELECT
HeaderValue1,
HeaderValue2
FROM HeaderTable
FOR
XML PATH('OH') ,
TYPE
) AS 'Header'
,(Select
DetailValue1,
DetailValue2
From DetailTable
FOR
XML PATH('OD') ,
TYPE
) AS 'LineItem'
From Table
FOR
XML PATH ,
ELEMENTS ,
ROOT('OH')
)
SELECT CONVERT(VARCHAR(MAX), XMLDocument)
FROM #tmp_Header
June 27, 2012 at 7:41 pm
You may not need the temp table. In fact, the query seems rather complicated for what you're trying to do.
I created 2 tables to test this:
Headers
----------------
HeaderID (PK)
Header
Details
----------------
DetailID (PK)
Detail
HeaderID (FK)
I populated a few header records and 2 matching detail records for each one in the table "Details" and used the following query:
select 1 as SortOrder, H.Header, H.HeaderID
from dbo.Headers H inner join dbo.Details D
on H.HeaderID = D.HeaderID
union
select 2 as SortOrder, D.Detail, H.HeaderID
from dbo.Headers H inner join dbo.Details D
on H.HeaderID = D.HeaderID
order by H.HeaderID, SortOrder
This produces the following results:
SortOrder | Header | HeaderID
-------------------------------------------------
1 | Header1 | 1
2 | Detail1 | 1
1 | Header2 | 1
2 | Detail2 | 1
If you want the results in XML, add the following to the end of the query:
for xml raw('Record'), root('Records'), elements
Does that work for you?
Mark
June 27, 2012 at 9:39 pm
What relates the header table to the details table?
Given what you're describing it sounds like you need to put it in as a correlated sub-query. Something like (this is just the XML part so you can review):
with headertable as
(
select 'Tools' HeaderValue1 ,'hand toools' HeaderValue2, 1 headerID
union all
select 'Tools' HeaderValue1 ,'hardware' HeaderValue2, 2 headerID),
detailtable as
(
select 'hammer' detailValue1 ,3 DetailValue2, 1 headerID
union all
select 'screwdrivers' ,14 ,1 headerID
union all
select 'saw' ,14 , 1 headerID
union all
select 'screws' ,154 , 2 headerID
union all
select 'bolts' ,147 , 2 headerID
)
SELECT
HeaderValue1,
HeaderValue2
,(Select
DetailValue1,
DetailValue2
From DetailTable
where headertable.headerID=detailtable.headerID -- this would be where you "join" the header to the detail
FOR
XML PATH('OD') ,
TYPE
)
FROM HeaderTable
FOR XML PATH('OH') ,TYPE
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 28, 2012 at 8:57 am
Hello Matt,
Thanks for getting back to me this worked perfect for what I was trying to do.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply