Parsing or getting tables from XML type column

Question

I have a column of XML data that contains thousands of records/cells. Each cell within this column takes this form (below is what one cell looks like - suppose it is Cell 3):

<Document>
  <Version>1</Version>
   <Section Name="General">
     <Value Name="Transaction type">CCM</Value>
     <Value Name="Description">Benjamin Capital</Value>
     <Value Name="Our likely role">Lead manager</Value>
     <Value Name="Target description">structure (Dec</Value>
     <Value Name="Previously submitted by email">No</Value>
   </Section>

  <Table Name="Team Members">
     <Headers>
       <Field Width="30">Name</Field>
       <Field Width="18">Country</Field>
       <Field Width="25">Department</Field>
       <Field Width="25">Role</Field>
     </Headers>
     <Row>
       <Field>Lincoln</Field>
       <Field>Australia</Field>
       <Field>Sales</Field>
       <Field>Manager</Field>
     </Row>
     <Row>
       <Field>Andrew</Field>
       <Field>Vietnam</Field>
       <Field>Estate</Field>
       <Field>Director</Field>
     </Row>
    </Table>

 <Table Name="Companies">
    <Headers>
      <Field Width="50">Party Name</Field>
      <Field Width="25">Role</Field>
      <Field Width="23">Contact Required?</Field>
    </Headers>
    <Row>
      <Field>A2 Milk</Field>
      <Field>Client</Field>
      <Field>Yes</Field>
    </Row>
    <Row>
      <Field>Citi Bank</Field>
      <Field>Client</Field>
      <Field>No</Field>
    </Row>
    <Row>
      <Field>McKinsey Co</Field>
      <Field>Provider</Field>
      <Field>No</Field>
    </Row>
    </Table>
</Document>

As you could see there are two tables embedded within one cell, Team Members and Companies. Ideally, I wish to turn these two big tables to this format, using maybe two separate queries:

Team Members Table

Cell No   |   Name     |  Country     | Department    |   Role
Cell 3    | Lincoln    |  Australia   |  Sales        |    Manager
Cell 3    | Andrew     |  Vietnam     | Estate        |   Director
Cell 4    | ....       |   ....       |  ...          |    ...
Cell 4    | ....       |  ....        |  ...          |    ...

Companies Table

Cell No   |   Party Name  |     Role    |  Contact_Required
Cell 3    |    A2 Milk    |    Client   |     Yes
Cell 3    |   Citi Bank   |   Client    |      Yes
Cell 3    |   McKinsey Co |   Provider  |       No
Cell 4    |  ....         |    ....     |     ...
Cell 4    |  ....         |    ....     |      ...
Cell 5    |  ....         |    ....     |     ...

The number of members and companies for each cell can be different. Please could you help me with this.

You may have a good starting point from this existing question: https://stackoverflow.com/questions/3989395/convert-xml-to-table-sql-server — The Betpet, Sep 27 '19 at 08:16

score 0 · Accepted Answer · answered Sep 27 '19 at 15:31

I'm afraid there's nothing out of the box.

T-SQL might be the wrong tool for this...

The need of variable table and column names cries for dynamically created SQL. But you can try something along this:

Create a mockup-scenario

DECLARE @mokupTable TABLE(ID INT,YourXmlColumn XML);
INSERT INTO @mokupTable VALUES
(1,N'<Document>
  <Version>1</Version>
   <Section Name="General">
     <Value Name="Transaction type">CCM</Value>
     <Value Name="Description">Benjamin Capital</Value>
     <Value Name="Our likely role">Lead manager</Value>
     <Value Name="Target description">structure (Dec</Value>
     <Value Name="Previously submitted by email">No</Value>
   </Section>

  <Table Name="Team Members">
     <Headers>
       <Field Width="30">Name</Field>
       <Field Width="18">Country</Field>
       <Field Width="25">Department</Field>
       <Field Width="25">Role</Field>
     </Headers>
     <Row>
       <Field>Lincoln</Field>
       <Field>Australia</Field>
       <Field>Sales</Field>
       <Field>Manager</Field>
     </Row>
     <Row>
       <Field>Andrew</Field>
       <Field>Vietnam</Field>
       <Field>Estate</Field>
       <Field>Director</Field>
     </Row>
    </Table>

 <Table Name="Companies">
    <Headers>
      <Field Width="50">Party Name</Field>
      <Field Width="25">Role</Field>
      <Field Width="23">Contact Required?</Field>
    </Headers>
    <Row>
      <Field>A2 Milk</Field>
      <Field>Client</Field>
      <Field>Yes</Field>
    </Row>
    <Row>
      <Field>Citi Bank</Field>
      <Field>Client</Field>
      <Field>No</Field>
    </Row>
    <Row>
      <Field>McKinsey Co</Field>
      <Field>Provider</Field>
      <Field>No</Field>
    </Row>
    </Table>
</Document>');

--Declare a table to store the generated commands

DECLARE @commands TABLE(ID INT IDENTITY,cmd NVARCHAR(MAX));

--This will insert the CREATE TABLE statements

INSERT INTO @commands
SELECT CONCAT(N'CREATE TABLE dbo.',QUOTENAME(A.t.value('@Name','nvarchar(1000)')),N'('
             ,STUFF((SELECT CONCAT(',',QUOTENAME(B.f.value('text()[1]','nvarchar(1000)')),N' NVARCHAR(',B.f.value('@Width','int'),')' )
                     FROM A.t.nodes('Headers/Field') B(f)
                     FOR XML PATH(''),TYPE).value('.','nvarchar(max)'),1,1,'')
             ,N');') 
FROM @mokupTable t
CROSS APPLY t.YourXmlColumn.nodes('/Document/Table') A(t)

--And this will generate the INSERT statements

INSERT INTO @commands
SELECT CONCAT(N'INSERT INTO dbo.',QUOTENAME(A.t.value('@Name','nvarchar(1000)')),N'('
             ,STUFF((SELECT CONCAT(',',QUOTENAME(B.f.value('text()[1]','nvarchar(1000)')))
                     FROM A.t.nodes('Headers/Field') B(f)
                     FOR XML PATH(''),TYPE).value('.','nvarchar(max)'),1,1,'')
             ,N') VALUES '
            ,STUFF((SELECT CONCAT(N',('
                                 ,STUFF((SELECT CONCAT(',''',QUOTENAME(C.f.value('text()[1]','nvarchar(1000)')),N'''' )
                                         FROM B.r.nodes('Field') C(f)
                                         FOR XML PATH(''),TYPE).value('.','nvarchar(max)'),1,1,'')
                                 ,')')   
                    FROM A.t.nodes('Row') B(r)
                    FOR XML PATH(''),TYPE).value('.','nvarchar(max)'),1,1,'')
             ,N';')
FROM @mokupTable t
CROSS APPLY t.YourXmlColumn.nodes('/Document/Table') A(t)

--Use a CURSOR to run through the commands and print them out for testing

DECLARE @cmd NVARCHAR(MAX);
DECLARE cur CURSOR FOR SELECT cmd FROM @commands ORDER BY ID;
OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS=0
BEGIN
    --Use EXEC(@cmd); to execute the statement
    PRINT @cmd;
    FETCH NEXT FROM cur INTO @cmd;  
END
CLOSE cur;
DEALLOCATE cur;

The code will create and print statements like these

CREATE TABLE dbo.[Team Members]([Name] NVARCHAR(30),[Country] NVARCHAR(18),[Department] NVARCHAR(25),[Role] NVARCHAR(25));
CREATE TABLE dbo.[Companies]([Party Name] NVARCHAR(50),[Role] NVARCHAR(25),[Contact Required?] NVARCHAR(23));
INSERT INTO dbo.[Team Members]([Name],[Country],[Department],[Role]) VALUES ('[Lincoln]','[Australia]','[Sales]','[Manager]'),('[Andrew]','[Vietnam]','[Estate]','[Director]');
INSERT INTO dbo.[Companies]([Party Name],[Role],[Contact Required?]) VALUES ('[A2 Milk]','[Client]','[Yes]'),('[Citi Bank]','[Client]','[No]'),('[McKinsey Co]','[Provider]','[No]');

Just use EXEC(@cmd); to execute this. Use a test db and give it a try :-)

@WillV Hi, just saw one error... Please remove the QUOTENAME() where the insert statement fills the values... Posting by phone at the moment... — Shnugo, Sep 28 '19 at 09:16
I wanted to upvote your comment but could not as I have a new profile — Will V, Sep 30 '19 at 03:14
@WillV Never mind, you can come back later. For now, you can accept the answer (the check mark). Votes and acceptance are two different kind of actions. Happy Coding — Shnugo, Sep 30 '19 at 04:48

Parsing or getting tables from XML type column

1 Answers1