using xquery to populate a table variable in sql
-
Hi, Just would like to ask for help for the xquery that I need to use for the xml string below to populate my table with the following dimension. This means that for each manager, I can have more than 1 row (depending on the number of staff) as I have flattened the table to eliminate joins on multiple table vars. Sorry, I just couldn't get my xquery right.
Finally, if I have my xsd, how do I validate an xml input such as above with sql?
Many thanks.Manager Name Type Staff
declare @myOrg xml
set @myOrg = '<?xml version="1.0" encoding="utf-8"?>
<Department>
<Subdivision>
<Managers>
<Manager>
<Name>Martin</Name>
<Type>Full Time</Type>
<Purpose>Subdivision 1 Shadow</Purpose>
<Description>Project Management, Operations</Description>
<FullName>Martin Fuller</FullName>
<StaffList>
<Staff>H1 Level 4, 345Y</Staff>
<Staff>H1 Level 4, DS23</Staff>
</StaffList>
</Manager>
<Manager>
<Name></Name>
<Type></Type>
<Purpose></Purpose>
<Description></Description>
<FullName></FullName>
<StaffList>
<Staff></Staff>
</StaffList>
</Manager>
</Managers>
</Subdivision>
</Department>'---------------------------------------------------------- Lorem ipsum dolor sit amet.