Load XML file into MS SQL Server table

Question

I asked this previously around 6 weeks ago but I wasn't particularly clear in terms of what I posted, what I expected and how it could be achieved. Despite the patience of some users, I was unable to solve my issue. I've gone back and tried to learn a bit more and hopefully I can now post something that makes more sense.

A customer sends his delivery schedules in the format of an XML file. It's not the most eloquent of files but I think I've made sense of it. What I need to get from it is a table like this, that tells me what quantity the customer wants of any given part on any given week. There's 50+ parts and 12 weeks but I've reduced it to 5 in the hope it can be posted here ok.

Schedule Date    PartNumber   Week Number   Quantity
20/02/2020       Part0        0             0
20/02/2020       Part0        1             50
20/02/2020       Part0        2             0
20/02/2020       Part0        3             0
20/02/2020       Part0        4             50
20/02/2020       Part0        5             0
20/02/2020       Part1        0             0
20/02/2020       Part1        1             40

What I'll be doing is appending this information to a table every week and the schedule date is the date of the schedule. This date is taken from this line (line 11):

<Field Name="DataDate2" FieldName="DataDate"><FormattedValue>10/02/2020</FormattedValue><Value>2020-02-10</Value></Field>

The part number, Week Number & Quantity are slightly more complicated. The part number comes from a list of numbers

<RowGroups>
<RowGroup>
<RowGroup>
<RowTotal RowNumber="0">PART0</RowTotal>
<RowTotal RowNumber="1">PART1</RowTotal>
<RowTotal RowNumber="2">PART2</RowTotal>
<RowTotal RowNumber="3">PART3</RowTotal>
<RowTotal RowNumber="4">PART4</RowTotal>
<RowTotal RowNumber="5">PART5</RowTotal>
</RowGroup>
</RowGroup>
</RowGroups>

and the week number & quantity from this section;

<Cells>
<Cell RowNumber="0" ColumnNumber="0">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="0" ColumnNumber="1">
<CellValue Index="0"><FormattedValue>50</FormattedValue><Value>50.00</Value></CellValue>
</Cell>

where the row number is the part number reference and the column number the week. The value is the value (and formatted value).

What I was thinking was creating two tables, a reference table with all the part numbers & row numbers and a second table with the row number, week number and quantity, then join them together on the row field. Either of the tables can hold the Schedule Date.

Could someone please give me some code to help me achieve this.

I've tried this as a starting point to create the first table of part numbers but this just returns 0 records:


DECLARE @xml XML
SELECT @xml =
CONVERT(XML,bulkcolumn,2) FROM OPENROWSET(BULK 'theFile.xml',SINGLE_BLOB) AS X

DECLARE @tempTable TABLE (
partnumber NVARCHAR(50),
rownumber int
)

INSERT INTO @tempTable
SELECT  Tbl.Col.value('@RowNumber', 'INT'),
        Tbl.Col.value('@RowTotal','NVARCHAR(50)')
FROM   @xml.nodes('//RowGroup') Tbl(Col)

SELECT * FROM @tempTable

Here is the XML:

<?xml version="1.0" encoding="UTF-8" ?>
<CrystalReport xmlns="urn:crystal-reports:schemas:report-detail"  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:crystal-reports:schemas:report-detail http://www.businessobjects.com/products/xml/CR2008Schema.xsd">
<ReportHeader>
</ReportHeader>
<Group Level="1">
<GroupHeader>
<Section SectionNumber="0">
<Text Name="Text4"><TextValue>DELIVERY SCHEDULE</TextValue>
</Text>
<Field Name="DataTime2" FieldName="DataTime"><FormattedValue>09:00:04</FormattedValue><Value>09:00:04</Value></Field>
<Field Name="DataDate2" FieldName="DataDate"><FormattedValue>10/02/2020</FormattedValue><Value>2020-02-10</Value></Field>
<Picture Name="Picture1" GraphicType="OLE">
</Picture>
<Text Name="Text13"><TextValue>DELIVERY ADDRESS</TextValue>
</Text>
<Text Name="Text8"><TextValue>SUPPLIER ADDRESS</TextValue>
</Text>
</Section>
<Section SectionNumber="1">
<Text Name="Text5"><TextValue>our details

</TextValue>
</Text>
<Field Name="WHS2" FieldName="{@WHS}"><FormattedValue>customer details
UK</FormattedValue><Value> customer details2
UK</Value></Field>
</Section>
<Section SectionNumber="2">
<Text Name="Text7"><TextValue>IMPORTANT

EACH ORDER REQUIRES A SEPERATE DELIVERY NOTE PER DELIVERY ADDRESS
</TextValue>
</Text>
</Section>
</GroupHeader>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<GroupFooter>
<Section SectionNumber="1">
<CrossTab Name="CrossTab2">
<RowGroups>
<RowGroup>
<RowGroup>
<RowTotal RowNumber="0">PART0</RowTotal>
<RowTotal RowNumber="1">PART1</RowTotal>
<RowTotal RowNumber="2">PART2</RowTotal>
<RowTotal RowNumber="3">PART3</RowTotal>
<RowTotal RowNumber="4">PART4</RowTotal>
<RowTotal RowNumber="5">PART5</RowTotal>
</RowGroup>
</RowGroup>
</RowGroups>
<ColumnGroups>
<ColumnGroup>
<ColumnGroup>
<ColumnTotal ColumnNumber="0"></ColumnTotal>
<ColumnTotal ColumnNumber="1"></ColumnTotal>
<ColumnTotal ColumnNumber="2"></ColumnTotal>
<ColumnTotal ColumnNumber="3"></ColumnTotal>
<ColumnTotal ColumnNumber="4"></ColumnTotal>
<ColumnTotal ColumnNumber="5"></ColumnTotal>

</ColumnGroup>
</ColumnGroup>
</ColumnGroups>
<Cells>
<Cell RowNumber="0" ColumnNumber="0">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="0" ColumnNumber="1">
<CellValue Index="0"><FormattedValue>50</FormattedValue><Value>50.00</Value></CellValue>
</Cell>
<Cell RowNumber="0" ColumnNumber="2">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="0" ColumnNumber="3">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="0" ColumnNumber="4">
<CellValue Index="0"><FormattedValue>50</FormattedValue><Value>50.00</Value></CellValue>
</Cell>
<Cell RowNumber="0" ColumnNumber="5">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="1" ColumnNumber="0">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="1" ColumnNumber="1">
<CellValue Index="0"><FormattedValue>40</FormattedValue><Value>40.00</Value></CellValue>
</Cell>
<Cell RowNumber="1" ColumnNumber="2">
<CellValue Index="0"><FormattedValue>40</FormattedValue><Value>40.00</Value></CellValue>
</Cell>
<Cell RowNumber="1" ColumnNumber="3">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="1" ColumnNumber="4">
<CellValue Index="0"><FormattedValue>20</FormattedValue><Value>20.00</Value></CellValue>
</Cell>
<Cell RowNumber="1" ColumnNumber="5">
<CellValue Index="0"><FormattedValue>40</FormattedValue><Value>40.00</Value></CellValue>
</Cell>
<Cell RowNumber="2" ColumnNumber="0">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="2" ColumnNumber="1">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="2" ColumnNumber="2">
<CellValue Index="0"><FormattedValue>50</FormattedValue><Value>50.00</Value></CellValue>
</Cell>
<Cell RowNumber="2" ColumnNumber="3">
<CellValue Index="0"><FormattedValue>50</FormattedValue><Value>50.00</Value></CellValue>
</Cell>
<Cell RowNumber="2" ColumnNumber="4">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="2" ColumnNumber="5">
<CellValue Index="0"><FormattedValue>50</FormattedValue><Value>50.00</Value></CellValue>
</Cell>
<Cell RowNumber="3" ColumnNumber="0">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="3" ColumnNumber="1">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="3" ColumnNumber="2">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="3" ColumnNumber="3">
<CellValue Index="0"><FormattedValue>10</FormattedValue><Value>10.00</Value></CellValue>
</Cell>
<Cell RowNumber="3" ColumnNumber="4">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="3" ColumnNumber="5">
<CellValue Index="0"><FormattedValue>10</FormattedValue><Value>10.00</Value></CellValue>
</Cell>
<Cell RowNumber="4" ColumnNumber="0">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="4" ColumnNumber="1">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="4" ColumnNumber="2">
<CellValue Index="0"><FormattedValue>40</FormattedValue><Value>40.00</Value></CellValue>
</Cell>
<Cell RowNumber="4" ColumnNumber="3">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="4" ColumnNumber="4">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="4" ColumnNumber="5">
<CellValue Index="0"><FormattedValue>40</FormattedValue><Value>40.00</Value></CellValue>
</Cell>
<Cell RowNumber="5" ColumnNumber="0">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="5" ColumnNumber="1">
<CellValue Index="0"><FormattedValue>20</FormattedValue><Value>20.00</Value></CellValue>
</Cell>
<Cell RowNumber="5" ColumnNumber="2">
<CellValue Index="0"><FormattedValue>20</FormattedValue><Value>20.00</Value></CellValue>
</Cell>
<Cell RowNumber="5" ColumnNumber="3">
<CellValue Index="0"><FormattedValue>10</FormattedValue><Value>10.00</Value></CellValue>
</Cell>
<Cell RowNumber="5" ColumnNumber="4">
<CellValue Index="0"><FormattedValue>10</FormattedValue><Value>10.00</Value></CellValue>
</Cell>
<Cell RowNumber="5" ColumnNumber="5">
<CellValue Index="0"><FormattedValue>10</FormattedValue><Value>10.00</Value></CellValue>
</Cell>
</Cells>
</CrossTab>
</Section>
</GroupFooter>
</Group>
<Group Level="1">
<GroupHeader>
<Section SectionNumber="0">
<Text Name="Text4"><TextValue>DELIVERY SCHEDULE</TextValue>
</Text>
<Field Name="DataTime2" FieldName="DataTime"><FormattedValue>09:00:04</FormattedValue><Value>09:00:04</Value></Field>
<Field Name="DataDate2" FieldName="DataDate"><FormattedValue>10/02/2020</FormattedValue><Value>2020-02-10</Value></Field>
<Picture Name="Picture1" GraphicType="OLE">
</Picture>
<Text Name="Text13"><TextValue>DELIVERY ADDRESS</TextValue>
</Text>
<Text Name="Text8"><TextValue>SUPPLIER ADDRESS</TextValue>
</Text>
</Section>
<Section SectionNumber="1">
<Text Name="Text5"><TextValue>our details

</TextValue>
</Text>
<Field Name="WHS2" FieldName="{@WHS}"><FormattedValue>customerdetails2
UK</FormattedValue><Value>customer details 2
UK</Value></Field>
</Section>
<Section SectionNumber="2">
<Text Name="Text7"><TextValue>IMPORTANT

EACH ORDER REQUIRES A SEPERATE DELIVERY NOTE PER DELIVERY ADDRESS
</TextValue>
</Text>
</Section>
</GroupHeader>
<Group Level="2">
</Group>
<GroupFooter>
<Section SectionNumber="1">
<CrossTab Name="CrossTab2">
<RowGroups>
<RowGroup>
<RowGroup>
<RowTotal RowNumber="0">P22031</RowTotal>
</RowGroup>
</RowGroup>
</RowGroups>
<ColumnGroups>
<ColumnGroup>
<ColumnGroup>
<ColumnTotal ColumnNumber="0"></ColumnTotal>
</ColumnGroup>
</ColumnGroup>
</ColumnGroups>
<Cells>
<Cell RowNumber="0" ColumnNumber="0">
<CellValue Index="0"><FormattedValue>4</FormattedValue><Value>4.00</Value></CellValue>
</Cell>
</Cells>
</CrossTab>
</Section>
</GroupFooter>
</Group>
<Group Level="1">
<GroupHeader>
<Section SectionNumber="0">
<Text Name="Text4"><TextValue>DELIVERY SCHEDULE</TextValue>
</Text>
<Field Name="DataTime2" FieldName="DataTime"><FormattedValue>09:00:04</FormattedValue><Value>09:00:04</Value></Field>
<Field Name="DataDate2" FieldName="DataDate"><FormattedValue>10/02/2020</FormattedValue><Value>2020-02-10</Value></Field>
<Picture Name="Picture1" GraphicType="OLE">
</Picture>
<Text Name="Text13"><TextValue>DELIVERY ADDRESS</TextValue>
</Text>
<Text Name="Text8"><TextValue>SUPPLIER ADDRESS</TextValue>
</Text>
</Section>
<Section SectionNumber="1">
<Text Name="Text5"><TextValue>our details

</TextValue>
</Text>
<Field Name="WHS2" FieldName="{@WHS}"><FormattedValue>2nd delivery address
UK</FormattedValue><Value> 2nd delivery address
UK</Value></Field>
</Section>
<Section SectionNumber="2">
<Text Name="Text7"><TextValue>IMPORTANT

EACH ORDER REQUIRES A SEPERATE DELIVERY NOTE PER DELIVERY ADDRESS
</TextValue>
</Text>
</Section>
</GroupHeader>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<GroupFooter>
<Section SectionNumber="1">
<CrossTab Name="CrossTab2">
<RowGroups>
<RowGroup>
<RowGroup>
<RowTotal RowNumber="0">Part12</RowTotal>
</RowGroup>
</RowGroup>
</RowGroups>
<ColumnGroups>
<ColumnGroup>
<ColumnGroup>
<ColumnTotal ColumnNumber="0"></ColumnTotal>
<ColumnTotal ColumnNumber="1"></ColumnTotal>
<ColumnTotal ColumnNumber="2"></ColumnTotal>
<ColumnTotal ColumnNumber="3"></ColumnTotal>
<ColumnTotal ColumnNumber="4"></ColumnTotal>
<ColumnTotal ColumnNumber="5"></ColumnTotal>
</ColumnGroup>
</ColumnGroup>
</ColumnGroups>
<Cells>
<Cell RowNumber="0" ColumnNumber="0">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="0" ColumnNumber="1">
<CellValue Index="0"><FormattedValue>5</FormattedValue><Value>5.00</Value></CellValue>
</Cell>
<Cell RowNumber="0" ColumnNumber="2">
<CellValue Index="0"><FormattedValue>5</FormattedValue><Value>5.00</Value></CellValue>
</Cell>
<Cell RowNumber="0" ColumnNumber="3">
<CellValue Index="0"><FormattedValue>7</FormattedValue><Value>7.00</Value></CellValue>
</Cell>
<Cell RowNumber="0" ColumnNumber="4">
<CellValue Index="0"><FormattedValue>6</FormattedValue><Value>6.00</Value></CellValue>
</Cell>
<Cell RowNumber="0" ColumnNumber="5">
<CellValue Index="0"><FormattedValue>5</FormattedValue><Value>5.00</Value></CellValue>
</Cell>
</Cells>
</CrossTab>
</Section>
</GroupFooter>
</Group>
<ReportFooter>
<Section SectionNumber="0">
</Section>
</ReportFooter>
</CrystalReport>

To make matters a little worse, there are two delivery addresses and the bottom section parts (there is only one part) is listed separately. It looks like these are only differentiated by the delivery addresses and nothing else. I think I'm happy to ignore this for now, unless it is easily worked around.

Thanks in advance.

Yitzhak Khabinsky · Accepted Answer · 2020-05-06 15:50:06Z

1

I saved you XML file as 'e:\Temp\CrystalReport.xml'. Your XML has namespaces, and they need special treatment. WITH XMLNAMESPACES clause takes care of it.

Please see below how to load an XML file into a DB table.

SQL

DECLARE @tempTable TABLE (ID INT IDENTITY PRIMARY KEY,  rownumber INT, partnumber NVARCHAR(50));

-- directly from the XML file as a virtual DB table on the file system
;WITH XMLNAMESPACES (DEFAULT 'urn:crystal-reports:schemas:report-detail')
, rs (xmldata) AS
(
   SELECT TRY_CAST(BulkColumn AS XML) AS BulkColumn 
   FROM OPENROWSET(BULK 'e:\Temp\CrystalReport.xml', SINGLE_BLOB) AS x
)
INSERT INTO @tempTable (rownumber, partnumber)
SELECT c.value('(./@RowNumber)[1]', 'INT') AS RowNumber
    , c.value('(./text())[1]','NVARCHAR(50)') AS RowTotal
FROM rs AS tbl
   CROSS APPLY tbl.xmldata.nodes('/CrystalReport/Group/GroupFooter/Section/CrossTab/RowGroups/RowGroup/RowGroup/RowTotal') AS t(c);

-- test
SELECT * FROM @tempTable;

Output

+----+-----------+------------+
| ID | rownumber | partnumber |
+----+-----------+------------+
|  1 |         0 | PART0      |
|  2 |         1 | PART1      |
|  3 |         2 | PART2      |
|  4 |         3 | PART3      |
|  5 |         4 | PART4      |
|  6 |         5 | PART5      |
|  7 |         0 | P22031     |
|  8 |         0 | Part12     |
+----+-----------+------------+

edited May 6, 2020 at 15:50

answered May 6, 2020 at 15:44

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Over a year ago

Thank you for taking the time to create this. It gives me the first part of what I need. I also need to create a second table with the RowNumber, ColumnNumber and value in it, so I can then join those tables to get my ultimate goal. I'm hoping that I can adapt your code to get the other part of what I need, thus using it as a starting point. Thanks again.

Gottfried Lesigang · Accepted Answer · 2020-05-06 18:32:29Z

I won't be going to read the whole and everything out of this XML, but - to show you the principles - I went quite some distance :-)

DECLARE @xml XML = 
N'<CrystalReport xmlns="urn:crystal-reports:schemas:report-detail"  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:crystal-reports:schemas:report-detail http://www.businessobjects.com/products/xml/CR2008Schema.xsd">
<ReportHeader>
</ReportHeader>
<Group Level="1">
<GroupHeader>
<Section SectionNumber="0">
<Text Name="Text4"><TextValue>DELIVERY SCHEDULE</TextValue>
</Text>
<Field Name="DataTime2" FieldName="DataTime"><FormattedValue>09:00:04</FormattedValue><Value>09:00:04</Value></Field>
<Field Name="DataDate2" FieldName="DataDate"><FormattedValue>10/02/2020</FormattedValue><Value>2020-02-10</Value></Field>
<Picture Name="Picture1" GraphicType="OLE">
</Picture>
<Text Name="Text13"><TextValue>DELIVERY ADDRESS</TextValue>
</Text>
<Text Name="Text8"><TextValue>SUPPLIER ADDRESS</TextValue>
</Text>
</Section>
<Section SectionNumber="1">
<Text Name="Text5"><TextValue>our details

</TextValue>
</Text>
<Field Name="WHS2" FieldName="{@WHS}"><FormattedValue>customer details
UK</FormattedValue><Value> customer details2
UK</Value></Field>
</Section>
<Section SectionNumber="2">
<Text Name="Text7"><TextValue>IMPORTANT

EACH ORDER REQUIRES A SEPERATE DELIVERY NOTE PER DELIVERY ADDRESS
</TextValue>
</Text>
</Section>
</GroupHeader>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<GroupFooter>
<Section SectionNumber="1">
<CrossTab Name="CrossTab2">
<RowGroups>
<RowGroup>
<RowGroup>
<RowTotal RowNumber="0">PART0</RowTotal>
<RowTotal RowNumber="1">PART1</RowTotal>
<RowTotal RowNumber="2">PART2</RowTotal>
<RowTotal RowNumber="3">PART3</RowTotal>
<RowTotal RowNumber="4">PART4</RowTotal>
<RowTotal RowNumber="5">PART5</RowTotal>
</RowGroup>
</RowGroup>
</RowGroups>
<ColumnGroups>
<ColumnGroup>
<ColumnGroup>
<ColumnTotal ColumnNumber="0"></ColumnTotal>
<ColumnTotal ColumnNumber="1"></ColumnTotal>
<ColumnTotal ColumnNumber="2"></ColumnTotal>
<ColumnTotal ColumnNumber="3"></ColumnTotal>
<ColumnTotal ColumnNumber="4"></ColumnTotal>
<ColumnTotal ColumnNumber="5"></ColumnTotal>

</ColumnGroup>
</ColumnGroup>
</ColumnGroups>
<Cells>
<Cell RowNumber="0" ColumnNumber="0">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="0" ColumnNumber="1">
<CellValue Index="0"><FormattedValue>50</FormattedValue><Value>50.00</Value></CellValue>
</Cell>
<Cell RowNumber="0" ColumnNumber="2">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="0" ColumnNumber="3">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="0" ColumnNumber="4">
<CellValue Index="0"><FormattedValue>50</FormattedValue><Value>50.00</Value></CellValue>
</Cell>
<Cell RowNumber="0" ColumnNumber="5">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="1" ColumnNumber="0">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="1" ColumnNumber="1">
<CellValue Index="0"><FormattedValue>40</FormattedValue><Value>40.00</Value></CellValue>
</Cell>
<Cell RowNumber="1" ColumnNumber="2">
<CellValue Index="0"><FormattedValue>40</FormattedValue><Value>40.00</Value></CellValue>
</Cell>
<Cell RowNumber="1" ColumnNumber="3">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="1" ColumnNumber="4">
<CellValue Index="0"><FormattedValue>20</FormattedValue><Value>20.00</Value></CellValue>
</Cell>
<Cell RowNumber="1" ColumnNumber="5">
<CellValue Index="0"><FormattedValue>40</FormattedValue><Value>40.00</Value></CellValue>
</Cell>
<Cell RowNumber="2" ColumnNumber="0">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="2" ColumnNumber="1">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="2" ColumnNumber="2">
<CellValue Index="0"><FormattedValue>50</FormattedValue><Value>50.00</Value></CellValue>
</Cell>
<Cell RowNumber="2" ColumnNumber="3">
<CellValue Index="0"><FormattedValue>50</FormattedValue><Value>50.00</Value></CellValue>
</Cell>
<Cell RowNumber="2" ColumnNumber="4">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="2" ColumnNumber="5">
<CellValue Index="0"><FormattedValue>50</FormattedValue><Value>50.00</Value></CellValue>
</Cell>
<Cell RowNumber="3" ColumnNumber="0">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="3" ColumnNumber="1">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="3" ColumnNumber="2">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="3" ColumnNumber="3">
<CellValue Index="0"><FormattedValue>10</FormattedValue><Value>10.00</Value></CellValue>
</Cell>
<Cell RowNumber="3" ColumnNumber="4">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="3" ColumnNumber="5">
<CellValue Index="0"><FormattedValue>10</FormattedValue><Value>10.00</Value></CellValue>
</Cell>
<Cell RowNumber="4" ColumnNumber="0">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="4" ColumnNumber="1">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="4" ColumnNumber="2">
<CellValue Index="0"><FormattedValue>40</FormattedValue><Value>40.00</Value></CellValue>
</Cell>
<Cell RowNumber="4" ColumnNumber="3">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="4" ColumnNumber="4">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="4" ColumnNumber="5">
<CellValue Index="0"><FormattedValue>40</FormattedValue><Value>40.00</Value></CellValue>
</Cell>
<Cell RowNumber="5" ColumnNumber="0">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="5" ColumnNumber="1">
<CellValue Index="0"><FormattedValue>20</FormattedValue><Value>20.00</Value></CellValue>
</Cell>
<Cell RowNumber="5" ColumnNumber="2">
<CellValue Index="0"><FormattedValue>20</FormattedValue><Value>20.00</Value></CellValue>
</Cell>
<Cell RowNumber="5" ColumnNumber="3">
<CellValue Index="0"><FormattedValue>10</FormattedValue><Value>10.00</Value></CellValue>
</Cell>
<Cell RowNumber="5" ColumnNumber="4">
<CellValue Index="0"><FormattedValue>10</FormattedValue><Value>10.00</Value></CellValue>
</Cell>
<Cell RowNumber="5" ColumnNumber="5">
<CellValue Index="0"><FormattedValue>10</FormattedValue><Value>10.00</Value></CellValue>
</Cell>
</Cells>
</CrossTab>
</Section>
</GroupFooter>
</Group>
<Group Level="1">
<GroupHeader>
<Section SectionNumber="0">
<Text Name="Text4"><TextValue>DELIVERY SCHEDULE</TextValue>
</Text>
<Field Name="DataTime2" FieldName="DataTime"><FormattedValue>09:00:04</FormattedValue><Value>09:00:04</Value></Field>
<Field Name="DataDate2" FieldName="DataDate"><FormattedValue>10/02/2020</FormattedValue><Value>2020-02-10</Value></Field>
<Picture Name="Picture1" GraphicType="OLE">
</Picture>
<Text Name="Text13"><TextValue>DELIVERY ADDRESS</TextValue>
</Text>
<Text Name="Text8"><TextValue>SUPPLIER ADDRESS</TextValue>
</Text>
</Section>
<Section SectionNumber="1">
<Text Name="Text5"><TextValue>our details

</TextValue>
</Text>
<Field Name="WHS2" FieldName="{@WHS}"><FormattedValue>customerdetails2
UK</FormattedValue><Value>customer details 2
UK</Value></Field>
</Section>
<Section SectionNumber="2">
<Text Name="Text7"><TextValue>IMPORTANT

EACH ORDER REQUIRES A SEPERATE DELIVERY NOTE PER DELIVERY ADDRESS
</TextValue>
</Text>
</Section>
</GroupHeader>
<Group Level="2">
</Group>
<GroupFooter>
<Section SectionNumber="1">
<CrossTab Name="CrossTab2">
<RowGroups>
<RowGroup>
<RowGroup>
<RowTotal RowNumber="0">P22031</RowTotal>
</RowGroup>
</RowGroup>
</RowGroups>
<ColumnGroups>
<ColumnGroup>
<ColumnGroup>
<ColumnTotal ColumnNumber="0"></ColumnTotal>
</ColumnGroup>
</ColumnGroup>
</ColumnGroups>
<Cells>
<Cell RowNumber="0" ColumnNumber="0">
<CellValue Index="0"><FormattedValue>4</FormattedValue><Value>4.00</Value></CellValue>
</Cell>
</Cells>
</CrossTab>
</Section>
</GroupFooter>
</Group>
<Group Level="1">
<GroupHeader>
<Section SectionNumber="0">
<Text Name="Text4"><TextValue>DELIVERY SCHEDULE</TextValue>
</Text>
<Field Name="DataTime2" FieldName="DataTime"><FormattedValue>09:00:04</FormattedValue><Value>09:00:04</Value></Field>
<Field Name="DataDate2" FieldName="DataDate"><FormattedValue>10/02/2020</FormattedValue><Value>2020-02-10</Value></Field>
<Picture Name="Picture1" GraphicType="OLE">
</Picture>
<Text Name="Text13"><TextValue>DELIVERY ADDRESS</TextValue>
</Text>
<Text Name="Text8"><TextValue>SUPPLIER ADDRESS</TextValue>
</Text>
</Section>
<Section SectionNumber="1">
<Text Name="Text5"><TextValue>our details

</TextValue>
</Text>
<Field Name="WHS2" FieldName="{@WHS}"><FormattedValue>2nd delivery address
UK</FormattedValue><Value> 2nd delivery address
UK</Value></Field>
</Section>
<Section SectionNumber="2">
<Text Name="Text7"><TextValue>IMPORTANT

EACH ORDER REQUIRES A SEPERATE DELIVERY NOTE PER DELIVERY ADDRESS
</TextValue>
</Text>
</Section>
</GroupHeader>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<Group Level="2">
</Group>
<GroupFooter>
<Section SectionNumber="1">
<CrossTab Name="CrossTab2">
<RowGroups>
<RowGroup>
<RowGroup>
<RowTotal RowNumber="0">Part12</RowTotal>
</RowGroup>
</RowGroup>
</RowGroups>
<ColumnGroups>
<ColumnGroup>
<ColumnGroup>
<ColumnTotal ColumnNumber="0"></ColumnTotal>
<ColumnTotal ColumnNumber="1"></ColumnTotal>
<ColumnTotal ColumnNumber="2"></ColumnTotal>
<ColumnTotal ColumnNumber="3"></ColumnTotal>
<ColumnTotal ColumnNumber="4"></ColumnTotal>
<ColumnTotal ColumnNumber="5"></ColumnTotal>
</ColumnGroup>
</ColumnGroup>
</ColumnGroups>
<Cells>
<Cell RowNumber="0" ColumnNumber="0">
<CellValue Index="0"><FormattedValue>0</FormattedValue><Value>0.00</Value></CellValue>
</Cell>
<Cell RowNumber="0" ColumnNumber="1">
<CellValue Index="0"><FormattedValue>5</FormattedValue><Value>5.00</Value></CellValue>
</Cell>
<Cell RowNumber="0" ColumnNumber="2">
<CellValue Index="0"><FormattedValue>5</FormattedValue><Value>5.00</Value></CellValue>
</Cell>
<Cell RowNumber="0" ColumnNumber="3">
<CellValue Index="0"><FormattedValue>7</FormattedValue><Value>7.00</Value></CellValue>
</Cell>
<Cell RowNumber="0" ColumnNumber="4">
<CellValue Index="0"><FormattedValue>6</FormattedValue><Value>6.00</Value></CellValue>
</Cell>
<Cell RowNumber="0" ColumnNumber="5">
<CellValue Index="0"><FormattedValue>5</FormattedValue><Value>5.00</Value></CellValue>
</Cell>
</Cells>
</CrossTab>
</Section>
</GroupFooter>
</Group>
<ReportFooter>
<Section SectionNumber="0">
</Section>
</ReportFooter>
</CrystalReport>';

--The query

WITH XMLNAMESPACES(DEFAULT 'urn:crystal-reports:schemas:report-detail')
,Level1Groups AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY A.gr) AS GroupCounter
          ,A.gr.value('@Level','int') AS GroupLevel
          ,A.gr.query('GroupHeader') AS GroupHeader
          ,A.gr.query('Group') AS NestedGroups
          ,A.gr.query('GroupFooter') AS GroupFooter
    FROM @xml.nodes('/CrystalReport/Group') A(gr)
)
,NestedData AS
(
    SELECT L1gr.*
          ,A.sec.value('@SectionNumber','int') AS SectionNumber
          ,B.CrTab.value('@Name','nvarchar(max)') AS CrossTab_Name
          ,C.rt.value('@RowNumber','int') AS RowNumber
          ,C.rt.value('text()[1]','nvarchar(max)') AS RowLeadText 
          --,B.CrTab.query('ColumnGroups/ColumnGroup/ColumnGroup') AS ColumnGroup
          ,B.CrTab.query('Cells') AS GroupCells
    FROm Level1Groups L1gr
    OUTER APPLY L1gr.GroupFooter.nodes('GroupFooter/Section') A(sec)
    OUTER APPLY A.sec.nodes('CrossTab') B(CrTab)
    OUTER APPLY B.CrTab.nodes('RowGroups/RowGroup/RowGroup/RowTotal') C(rt)
)
,ColumnValues AS
(
    SELECT *
          ,nd.GroupCells.query('for $c in distinct-values(Cells/Cell[@RowNumber=sql:column("nd.RowNumber")]/@ColumnNumber)
                               return <val ColumnNumber="{$c}">{Cells
                                           /Cell[@RowNumber=sql:column("nd.RowNumber") 
                                                 and @ColumnNumber=$c]
                                           /CellValue
                                           /FormattedValue
                                           /text()}</val>
                               ') AS RowValues
    FROM NestedData nd
)
/* to see everything returned use this 
SELECT cv.*
      ,A.vals.value('text()[1]','nvarchar(max)') AS CellValue
FROM ColumnValues cv
OUTER APPLY cv.RowValues.nodes('val') A(vals);
*/
SELECT cv.GroupHeader.value('(//Field[@FieldName="DataDate"]/FormattedValue/text())[1]','nvarchar(max)') As ScheduleDate
      ,cv.RowLeadText
      ,cv.RowNumber
      ,A.vals.value('@ColumnNumber','int') AS ColumnNumber
      ,A.vals.value('text()[1]','nvarchar(max)') AS CellValue
FROM ColumnValues cv
OUTER APPLY cv.RowValues.nodes('val') A(vals);

The result

+--------------+-------------+-----------+--------------+-----------+
| ScheduleDate | RowLeadText | RowNumber | ColumnNumber | CellValue |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART0       | 0         | 0            | 0         |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART0       | 0         | 1            | 50        |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART0       | 0         | 2            | 0         |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART0       | 0         | 3            | 0         |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART0       | 0         | 4            | 50        |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART0       | 0         | 5            | 0         |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART1       | 1         | 0            | 0         |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART1       | 1         | 1            | 40        |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART1       | 1         | 2            | 40        |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART1       | 1         | 3            | 0         |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART1       | 1         | 4            | 20        |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART1       | 1         | 5            | 40        |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART2       | 2         | 0            | 0         |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART2       | 2         | 1            | 0         |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART2       | 2         | 2            | 50        |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART2       | 2         | 3            | 50        |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART2       | 2         | 4            | 0         |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART2       | 2         | 5            | 50        |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART3       | 3         | 0            | 0         |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART3       | 3         | 1            | 0         |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART3       | 3         | 2            | 0         |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART3       | 3         | 3            | 10        |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART3       | 3         | 4            | 0         |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART3       | 3         | 5            | 10        |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART4       | 4         | 0            | 0         |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART4       | 4         | 1            | 0         |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART4       | 4         | 2            | 40        |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART4       | 4         | 3            | 0         |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART4       | 4         | 4            | 0         |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART4       | 4         | 5            | 40        |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART5       | 5         | 0            | 0         |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART5       | 5         | 1            | 20        |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART5       | 5         | 2            | 20        |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART5       | 5         | 3            | 10        |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART5       | 5         | 4            | 10        |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | PART5       | 5         | 5            | 10        |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | P22031      | 0         | 0            | 4         |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | Part12      | 0         | 0            | 0         |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | Part12      | 0         | 1            | 5         |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | Part12      | 0         | 2            | 5         |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | Part12      | 0         | 3            | 7         |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | Part12      | 0         | 4            | 6         |
+--------------+-------------+-----------+--------------+-----------+
| 10/02/2020   | Part12      | 0         | 5            | 5         |
+--------------+-------------+-----------+--------------+-----------+

In order to resolve the CrossTabs I use some XQuery FLWOR returning the values in a re-generated XML.

Hint: Take away the namespace (and the first WITH line) to see the result in a better readable format.

I hope this helps a bit...

you've clearly gone to a lot of trouble with this and I really appreciate it... unfortunately, most of this has gone totally over my head as I'm very inexperienced with xml and can only do basic sql. The output on your post is exactly what I'm looking for but running your script in the SSMS just gives many errors. Where would I put the path to the location of the XML file please? If you could dumb it right down for me, it'd be very much appreciated :)
@David, you can use the other answer's approach to read the XML into a table, a temp-table, a declared table variable or into a XML-typed variable directly. Once you have got the XML within SQL-Server you should be able to use the above... If you need further help, I'd suggest to start a new question with a specific question for a specific error. If this question answers this initial question (at least it returns what you asked for), it would be kind to close this question by acceptance and use your right to up-vote. In your new question place a link to this one. Help will come soon :-)

Collectives™ on Stack Overflow

Load XML file into MS SQL Server table

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related