Dynamic XML to SQL - SQL Server

Question

I have a very complex XML, I am just trying to write dynamic SQL query to insert the data into a SQL Server database from the XML.

Below is the XSD of the XML ::

<xs:element name="Department">
    <xs:complexType>
        <xs:all>
            <xs:element name="DeptID" type="xs:string" minOccurs="0" maxOccurs="1" />
            <xs:element name="DepRefNum" type="xs:string" minOccurs="0" maxOccurs="1" />
            <xs:element ref="date"/>            
            <xs:element ref="Employees" minOccurs="0" maxOccurs="1"/>
        </xs:all>
    </xs:complexType>
</xs:element>

<xs:element name="Employees">
    <xs:complexType>
        <xs:sequence>
            <xs:element ref="Employee" minOccurs="0" maxOccurs="unbounded"/>
        </xs:sequence>
    </xs:complexType>
</xs:element>

<xs:element name="Employee">
    <xs:complexType>
        <xs:sequence>
            <xs:element name="Name" minOccurs="0" maxOccurs="1"/>
            <xs:element name="Code" type="xs:string" minOccurs="0" maxOccurs="1"/>
            <xs:element ref="Date" minOccurs="0" maxOccurs="1"/>            
        </xs:sequence>
    </xs:complexType>
</xs:element>

<xs:element name="Date" type="TypeStringWithTypeAttribute"/>

<xs:complexType name="TypeStringWithTypeAttribute">
    <xs:simpleContent>
        <xs:extension base="xs:string">
            <xs:attribute name="type" type="xs:string"/>
        </xs:extension>
    </xs:simpleContent>
</xs:complexType>

This is a sample XML :

<Department>
   <DeptID>D101</DeptID>
   <DepRefNum></DepRefNum>
   <Date type="Create">01/1/2017 12:30</Date>
   <Employees>
         <Employee>
             <Name>Jason</Name>
             <Code>J111</Code>
             <Date type="DOJ">06/18/2018 14:36</Date>
         </Employee>
         <Employee>
              <Name>Roy</Name>
              <Code>R111</Code>
              <Date type="DOJ">06/18/2018 14:36</Date>
         </Employee>
    </Employees>   
</Department>

I have 2 tables, and I am trying to get the data in below format, where Dept_ID is from the XML, but EMP_ID is an identity and I want Dept_ID to be used as foreign key, that is not the concern but just for the info :

Department:

Dept_ID     Dept_Ref_Num    Create_Date_Type    Create_Date
---------------------------------------------------------------
D101                        Create              01/1/2017 12:30

Employees:

Emp_ID   Dept_ID    Emp_Name    Emp_Code    Date_Type   Date
-------------------------------------------------------------------------
   1     D101       Jason       J111        DOJ         06/18/2018 14:36
   2     D101       Roy         R111        DOJ         06/18/2018 14:36

I am facing issues because it is dynamic xml, I don't know how many employees would be there in real time xml , i.e. because employee under employees is unbounded.

Answer 1

You don't need dynamic SQL to extract the data here. You just need to query the proper node and access the parent, like this:

DECLARE @x XML='<Department>
   <DeptID>D101</DeptID>
   <DepRefNum></DepRefNum>
   <Date type="Create">01/1/2017 12:30</Date>
   <Employees>
         <Employee>
             <Name>Jason</Name>
             <Code>J111</Code>
             <Date type="DOJ">06/18/2018 14:36</Date>
         </Employee>

         <Employee>
              <Name>Roy</Name>
              <Code>R111</Code>
              <Date type="DOJ">06/18/2018 14:36</Date>
         </Employee>
    </Employees>
</Department>'

SELECT  n.value('DeptID[1]','varchar(10)') AS DeptID,
        n.value('DepRefNum[1]','varchar(10)') AS DepRefNum,
        n.value('Date[1]/@type','varchar(10)') AS Create_Date_Type,
        n.value('Date[1]','datetime') AS Create_Date
FROM @x.nodes('/Department') R(n)

SELECT  n.value('../../DeptID[1]','varchar(10)') AS DeptID,
        n.value('Name[1]','varchar(10)') AS Emp_Name,
        n.value('Code[1]','varchar(10)') AS Emp_Code,
        n.value('Date[1]/@type','varchar(10)') AS Date_Type,
        n.value('Date[1]','datetime') AS Date
FROM @x.nodes('/Department/Employees/Employee') R(n)

Answer 2

Corrected the close tags on the XML

Perhaps something like this:

Example

Declare @XML xml = '
<Department>
   <DeptID>D101</DeptID>
   <DepRefNum></DepRefNum>
   <Date type="Create">01/1/2017 12:30</Date>
   <Employees>
         <Employee>
             <Name>Jason</Name>
             <Code>J111</Code>
             <Date type="DOJ">06/18/2018 14:36</Date>
         </Employee>
         <Employee>
              <Name>Roy</Name>
              <Code>R111</Code>
              <Date type="DOJ">06/18/2018 14:36</Date>
         </Employee>
    </Employees>   
</Department>
'

Select DeptID    = lvl1.n.value('DeptID[1]','varchar(150)') 
      ,DepRefNum = lvl1.n.value('DepRefNum[1]','varchar(150)') 
      ,Create_Date_Type = lvl1.n.value('Date[1]/@type','varchar(150)') 
      ,Create_Date = lvl1.n.value('Date[1]','varchar(150)') 
 From  @XML.nodes('*') lvl1(n)

Select DeptID    = lvl1.n.value('DeptID[1]','varchar(150)') 
      ,Emp_Name  = lvl2.n.value('Name[1]','varchar(150)') 
      ,Emp_Code  = lvl2.n.value('Code[1]','varchar(150)') 
      ,Date_Type = lvl2.n.value('Date[1]/@type','varchar(150)') 
      ,Date      = lvl2.n.value('Date[1]','varchar(150)') 
 From  @XML.nodes('*') lvl1(n)
 Cross Apply lvl1.n.nodes('Employees/*') lvl2(n)

Returns

DeptID      DepRefNum   Create_Date_Type    Create_Date
D101                    Create              01/1/2017 12:30

and

DeptID  Emp_Name    Emp_Code    Date_Type   Date
D101    Jason       J111        DOJ         06/18/2018 14:36
D101    Roy         R111        DOJ         06/18/2018 14:36