July 29, 2014 at 5:54 am
Hi All,
I have several stored procedures that will query tables and return xml data. I would like to join these returned xml pieces into one large xml file, but I'm having trouble merging them all together at the correct hierarchical levels. It may be that I have set off down the wrong road and need to be moved onto another, but here is what I am trying to do at the moment:
One procedure will return the top level information into an xml variable (@TopLevel as demonstrated in the first example below), going down to bottom level of StoreNumber. All other procedures will create an xml variable with a top level of StoreNumber and then levels below that, group by group (in the example, Employees going into @EmployeeInfo, but there are other groups, like maybe @addressInfo and @TakingsInfo and the list will keep changing depending on a set of parametrised requirements).
So here is what @TopLevel might look like:
<Regions>
<Region>
<Name>North</Name>
<Stores>
<Store>
<StoreNumber>10</StoreNumber>
</Store>
</Stores>
</Region>
<Region>
<Name>South</Name>
<Stores>
<Store>
<StoreNumber>11</StoreNumber>
</Store>
<Store>
<StoreNumber>12</StoreNumber>
</Store>
</Stores>
</Region>
</Regions>
and the @EmployeeInfo might look like this:
<Store>
<StoreNumber>10</StoreNumber>
<Employees>
<Employee>
<Name>John</Name>
</Employee>
<Employee>
<Name>Beverley</Name>
</Employee>
</Employees>
</Store>
<Store>
<StoreNumber>11</StoreNumber>
<Employees>
<Employee>
<Name>Billie</Name>
</Employee>
</Employees>
</Store>
<Store>
<StoreNumber>12</StoreNumber>
<Employees>
<Employee>
<Name>Joel</Name>
</Employee>
</Employees>
</Store>
so my question is this - how would I combine these two bits of xml into something that looks like this:
<Regions>
<Region>
<Name>North</Name>
<Stores>
<Store>
<StoreNumber>10</StoreNumber>
<Employees>
<Employee>
<Name>John</Name>
</Employee>
<Employee>
<Name>Beverley</Name>
</Employee>
</Employees>
</Store>
</Stores>
</Region>
<Region>
<Name>South</Name>
<Stores>
<Store>
<StoreNumber>11</StoreNumber>
<Employees>
<Employee>
<Name>Billie</Name>
</Employee>
</Employees>
</Store>
<Store>
<StoreNumber>12</StoreNumber>
<Employees>
<Employee>
<Name>Joel</Name>
</Employee>
</Employees>
</Store>
</Stores>
</Region>
</Regions>
I had envisioned that the combining would be done by an master procedure, which would check some configuration table and would include the sub-store level details only as required.
If I were dealing with tables, I would join the @Toplevel table to the @EmployeeInfo table on @Toplevel.StoreNumber = @EmployeeInfo.StoreNumber - is there an equivalent method in xml? Given that I have created the xml from tables in the first place, I don't really want to convert the information back into tables to make the join - it seems good to me that each sub-store level is separated into a different procedure as it keeps everything discrete and tidy, but maybe this approach is unworkable?
I have made each bit of xml and have them all waiting here, but I just can't glue them together! Any help much appreciated.
July 29, 2014 at 1:21 pm
Here's an example which might prove useful:
declare @addinfo xml;
declare @empinfo xml;
declare @toplevel xml;
set @toplevel='<Regions>
<Region>
<Name>North</Name>
<Stores>
<Store>
<StoreNumber>10</StoreNumber>
</Store>
</Stores>
</Region>
<Region>
<Name>South</Name>
<Stores>
<Store>
<StoreNumber>11</StoreNumber>
</Store>
<Store>
<StoreNumber>12</StoreNumber>
</Store>
</Stores>
</Region>
</Regions>'
set @empinfo='<Store>
<StoreNumber>10</StoreNumber>
<Employees>
<Employee>
<Name>John</Name>
</Employee>
<Employee>
<Name>Beverley</Name>
</Employee>
</Employees>
</Store>
<Store>
<StoreNumber>11</StoreNumber>
<Employees>
<Employee>
<Name>Billie</Name>
</Employee>
</Employees>
</Store>
<Store>
<StoreNumber>12</StoreNumber>
<Employees>
<Employee>
<Name>Joel</Name>
</Employee>
</Employees>
</Store>'
set @addinfo='<Store>
<StoreNumber>10</StoreNumber>
<Address>
<Street> 123Main</Street>
<city>AnyTown</city>
</Address>
</Store>
<Store>
<StoreNumber>11</StoreNumber>
<Address>
<Street> 123Main</Street>
<city>BobVille</city>
</Address>
</Store>
<Store>
<StoreNumber>12</StoreNumber>
<Address>
<Street> 123Main</Street>
<city>JoeCity</city>
</Address>
</Store>'
;with TopCTE as (
selectc.value('(../../Name)[1]','varchar(500)') reg,
c.value('(./StoreNumber)[1]','int') snum,
c.query('.') Store
from @toplevel.nodes('/Regions/Region/Stores/Store') X(c)),
EmpCTE as (
select
c.value('(./StoreNumber)[1]','int') snum,
c.query('./Employees/*') Emp
from @empinfo.nodes('/Store') X(c)),
AddCTE as (
select
c.value('(./StoreNumber)[1]','int') snum,
c.query('./Address/*') Addr
from @addinfo.nodes('/Store') X(c))
Select TopOut.reg 'Name',
(Select topIn.snum 'StoreNumber',
(Select Emp from EmpCTE where topIn.snum=EmpCTE.snum for XML PATH(''), type) 'Employees',
(Select Addr from AddCTE where topIn.snum=AddCTE.snum for XML PATH(''), type) 'Address'
from TopCTE topIn where TopOut.reg=topIn.reg
for XML PATH('Store'), root('Stores'), TYPE)
from (select distinct reg from topcte) TopOut for XML PATh('Region'), root('Regions')
----------------------------------------------------------------------------------
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?
July 29, 2014 at 6:01 pm
Alternatively, a way that might help if the elements to be inserted can vary by each run - using XML.modify to insert the child elements into the TopLevel document.
Each child XML could easily be included or not by the use of IF statements.
-- Store the top level xml in a table (variable) so it can be used in an UPDATE statement
declare @t table(x xml);
insert @t(x) values(@toplevel);
--== Add the Employees nodes into the toplevel XML ==--
-- This requires a loop as xml.modify doesn't allow multiple updates in one batch
while exists(
-- check if there are any unprocessed elements in the empinfo xml fragment
select 1
from @t
cross apply x.nodes('//Store') tl(store)
cross apply (select tl.store.value('(StoreNumber/text())[1]','int')) as store(number)
cross apply (select @empinfo.query('(//Store[StoreNumber/text()=sql:column("store.number")]/Employees)[1]')) emp(nd)
where x.exist('(//Store[StoreNumber/text()=sql:column("store.number")]/Employees)[1]')=0
)
update top(1) t
set x.modify('insert sql:column("emp.nd") as last into (//Store[StoreNumber/text()=sql:column("store.number")])[1]')
from @t AS t
cross apply x.nodes('//Store') tl(store) -- Process each Store
cross apply (select tl.store.value('(StoreNumber/text())[1]','int')) as store(number) -- convert the StoreNumber to a non-xml data type
cross apply (select cast(@empinfo.query('(//Store[StoreNumber/text()=sql:column("store.number")]/Employees)[1]') as xml)) emp(nd) -- find the related Employees for this store
where x.exist('(//Store[StoreNumber/text()=sql:column("store.number")]/Employees)[1]')=0 -- prevent updates of existing elements
--== Add the Address nodes into the toplevel XML ==--
while exists(
select 1
from @t
cross apply x.nodes('//Store') tl(store)
cross apply (select tl.store.value('(StoreNumber/text())[1]','int')) as store(number)
cross apply (select [highlight="#ffff11"]@addinfo[/highlight].query('(//Store[StoreNumber/text()=sql:column("store.number")]/[highlight="#ffff11"]Address[/highlight])[1]')) [highlight="#ffff11"]addr[/highlight](nd)
where x.exist('(//Store[StoreNumber/text()=sql:column("store.number")]/[highlight="#ffff11"]Address[/highlight])[1]')=0
)
update top(1) t
set x.modify('insert sql:column("[highlight="#ffff11"]addr[/highlight].nd") as last into (//Store[StoreNumber/text()=sql:column("store.number")])[1]')
from @t AS t
cross apply x.nodes('//Store') tl(store)
cross apply (select tl.store.value('(StoreNumber/text())[1]','int')) as store(number)
cross apply (select cast([highlight="#ffff11"]@addinfo[/highlight].query('(//Store[StoreNumber/text()=sql:column("store.number")]/[highlight="#ffff11"]Address[/highlight])[1]') as xml)) [highlight="#ffff11"]addr[/highlight](nd)
where x.exist('(//Store[StoreNumber/text()=sql:column("store.number")]/[highlight="#ffff11"]Address[/highlight])[1]')=0
select x from @t
I have used the test data from Matt's answer, and have sections for Employees and Address.
Each new section you want to add in is then just a "copy and paste" of an existing section, then edit the highlighted parts (as seen in the Address section of my code).
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);July 30, 2014 at 2:05 am
Thank you both; I will have a look at the results from each method and see what can be done. Given that both of these methods require a bit of work to convert the xml back out to table structure before recombining, I think I may need to change my approach entirely. Rather than having several procedures all contributing xml to a larger document, it may be more appropriate to prepare my data in several tables and build the xml at the end in a single pass. I will talk with the other guys in the team and we'll see which way is best.
It has been an education and very helpful
Thanks again
Mark
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply