SQL Server 2008 OPENXML Insert

Question

I have an XML document that is pretty much structured like an Excel spreadsheet based on the schema. There are two worksheets in this file. How can I insert all the data from the second sheet into my SQL table? I basically want a straight import, but OPENXML keeps telling me 0 rows affected. Here is a sample of the XML doc:

<Worksheet ss:Name="Product Level Data">
<x:WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Print>
<ValidPrinterInfo/>
<PaperSizeIndex>1</PaperSizeIndex>
<Scale>100</Scale>
<FitWidth>1</FitWidth>
<FitHeight>1</FitHeight>
<HorizontalResolution>300</HorizontalResolution>
<VerticalResolution>300</VerticalResolution>
</Print>
<Zoom>100</Zoom>
<x:PageSetup>
<x:Header  ss:StyleID="systemtitle" Data="The SAS System"
/>
</x:PageSetup>
<Selected/>
<FreezePanes/>
<FrozenNoSplit/>
<SplitHorizontal>1</SplitHorizontal>
<TopRowBottomPane>1</TopRowBottomPane>
<ActivePane>2</ActivePane>
<Panes>
<Pane>
<Number>3</Number>
</Pane>
<Pane>
<Number>2</Number>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</x:WorksheetOptions>
<AutoFilter x:Range="R1C1:R545C10" xmlns="urn:schemas-microsoft-com:office:excel"></AutoFilter><Table ss:StyleID="_body">
<ss:Column ss:AutoFitWidth="1" ss:Width="90"/>
<ss:Column ss:AutoFitWidth="1" ss:Width="270"/>
<ss:Column ss:AutoFitWidth="1" ss:Width="90"/>
<ss:Column ss:AutoFitWidth="1" ss:Width="90"/>
<ss:Column ss:AutoFitWidth="1" ss:Width="90"/>
<ss:Column ss:AutoFitWidth="1" ss:Width="90"/>
<ss:Column ss:AutoFitWidth="1" ss:Width="90"/>
<ss:Column ss:AutoFitWidth="1" ss:Width="90"/>
<ss:Column ss:AutoFitWidth="1" ss:Width="108"/>
<ss:Column ss:AutoFitWidth="1" ss:Width="108"/>
<Row ss:AutoFitHeight="1">
<Cell ss:StyleID="header__c" ss:Index="1"><Data ss:Type="String">ID</Data></Cell>
<Cell ss:StyleID="header__c" ss:Index="2"><Data ss:Type="String">Company Name</Data></Cell>
<Cell ss:StyleID="header__l" ss:Index="3"><Data ss:Type="String">Item</Data></Cell>
<Cell ss:StyleID="header__c" ss:Index="4"><Data ss:Type="String">Type</Data></Cell>
<Cell ss:StyleID="header__r1" ss:Index="5"><Data ss:Type="String">Total Time</Data></Cell>
<Cell ss:StyleID="header__r1" ss:Index="6"><Data ss:Type="String">Total Amount</Data></Cell>
<Cell ss:StyleID="header__r1" ss:Index="7"><Data ss:Type="String">Count Product</Data></Cell>
<Cell ss:StyleID="header__r" ss:Index="8"><Data ss:Type="String">Percentage Time</Data></Cell>
<Cell ss:StyleID="header__r" ss:Index="9"><Data ss:Type="String">Product Time</Data></Cell>
<Cell ss:StyleID="header__r" ss:Index="10"><Data ss:Type="String">Invalid Product Time</Data></Cell>
</Row>
<Row ss:AutoFitHeight="1">
<Cell ss:StyleID="data__l" ss:Index="1"><Data ss:Type="String">DF</Data></Cell>
<Cell ss:StyleID="data__l" ss:Index="2"><Data ss:Type="String">Dan's Fruit Company</Data></Cell>
<Cell ss:StyleID="data__l" ss:Index="3"><Data ss:Type="String">Apple</Data></Cell>
<Cell ss:StyleID="data__r" ss:Index="4"><Data ss:Type="String">Fruit</Data></Cell>
<Cell ss:StyleID="data__r1" ss:Index="5"><Data ss:Type="Number">2034004</Data></Cell>
<Cell ss:StyleID="data__r1" ss:Index="6"><Data ss:Type="Number">23423</Data></Cell>
<Cell ss:StyleID="data__r1" ss:Index="7"><Data ss:Type="Number">15789</Data></Cell>
<Cell ss:StyleID="data__r" ss:Index="8"><Data ss:Type="Number">100.0</Data></Cell>
<Cell ss:StyleID="data__r" ss:Index="9"><Data ss:Type="Number">0.000</Data></Cell>
<Cell ss:StyleID="data__r" ss:Index="10"><Data ss:Type="Number">0.000</Data></Cell>
</Row>

Here is the code I am trying. This is the first time I've tried to use OPENXML:

DECLARE @XMLDocPointer INT
, @strXML VARCHAR(10000)

SET @strXML = <large code block up there>

EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @strXML

BEGIN TRANSACTION
INSERT INTO xml_staging_table
(
id
, company_name
, item
, type
, total_time
, total_amount
, count_product
, percent_time
, product_time
, invalid_product_time
)
SELECT id
, company_name
, item
, type
, total_time
, total_amount
, count_product
, percentage_time
, product_time
, invalid_product_time
FROM OPENXML(@XMLDocPointer,'/WORKSHEET/TABLE/ROW/CELL', 4) --What is the correct syntax here?
WITH 
(
id VARCHAR(10) 'id/.'
, company_name VARCHAR(500) 'company_name/.'
, item VARCHAR(10) 'item/.'
, type VARCHAR(50) 'type/.'
, total_time BIGINT 'total_time/.'
, total_amount BIGINT 'total_amount/.'
, count_product BIGINT 'count_product/.'
, percentage_time DECIMAL(5,2) 'percentage_time/.'
, product_time BIGINT 'product_time/.'
, invalid_product_time BIGINT 'invalid_product_time/.'
)
COMMIT

EXEC sp_xml_removedocument @XMLDocPointer

marc_s · Accepted Answer · 2012-05-17 15:50:08Z

2

Not sure how to do it with OPENQUERY - this is deprecated, you should use the XQuery support in SQL Server 2005 and newer directly.

Also: your XML is incomplete in that it doesn't show the definition of the ss: xml namespace anywhere - but ignoring that - you should be able to get your data with a SQL statement something like this:

-- reduced huge XML to the actual relevant parts...
-- defined "dummy" XML namespace for ss: prefix
DECLARE @input XML = '<Worksheet ss:Name="Product Level Data" xmlns:ss="urn:test">
<Table ss:StyleID="_body">
  <Row ss:AutoFitHeight="1">
    <Cell ss:StyleID="data__l" ss:Index="1"><Data ss:Type="String">DF</Data></Cell>
    <Cell ss:StyleID="data__l" ss:Index="2"><Data ss:Type="String">Dan''s Fruit Company</Data></Cell>
    <Cell ss:StyleID="data__l" ss:Index="3"><Data ss:Type="String">Apple</Data></Cell>
    <Cell ss:StyleID="data__r" ss:Index="4"><Data ss:Type="String">Fruit</Data></Cell>
    <Cell ss:StyleID="data__r1" ss:Index="5"><Data ss:Type="Number">2034004</Data></Cell>
    <Cell ss:StyleID="data__r1" ss:Index="6"><Data ss:Type="Number">23423</Data></Cell>
    <Cell ss:StyleID="data__r1" ss:Index="7"><Data ss:Type="Number">15789</Data></Cell>
    <Cell ss:StyleID="data__r" ss:Index="8"><Data ss:Type="Number">100.0</Data></Cell>
    <Cell ss:StyleID="data__r" ss:Index="9"><Data ss:Type="Number">0.000</Data></Cell>
    <Cell ss:StyleID="data__r" ss:Index="10"><Data ss:Type="Number">0.000</Data></Cell>
  </Row>
 </Table>
</Worksheet>'

SELECT
    XmlCell.value('(.)[1]', 'varchar(50)')
FROM
    @Input.nodes('/Worksheet/Table/Row/Cell/Data') AS Nodes(XmlCell)

This gives me an output of:

DF
Dan's Fruit Company
Apple
Fruit
2034004
23423
15789
100.0
0.000
0.000

Update: to handle the "full" input XML, and just extracting the cells that have a StyleID that is something like data...., you can use this T-SQL statement - again, it has to know the definition of the ss: XML namespace - I just "tweaked" it here to something I choose - replace this with your actual XML namespace for the ss: prefix:

;WITH XMLNAMESPACES('urn:test' AS ss), 
XmlParsedData AS
(
SELECT
    CellStyle = XmlCell.value('@ss:StyleID', 'varchar(50)'),
    CellIndex = XmlCell.value('@ss:Index', 'int'),
    CellValue = XmlCell.value('(Data)[1]', 'varchar(50)')
FROM
    @Input.nodes('/Worksheet/Table/Row/Cell') AS Nodes(XmlCell)
)
SELECT *
FROM XmlParsedData
WHERE
    CelLStyle LIKE 'data%'

This will give you an output like:

CellStyle  CellIndex  CellValue
data__l        1      DF
data__l        2      Dan's Fruit Company
data__l        3      Apple
data__r        4      Fruit
data__r1       5      2034004
data__r1       6      23423
data__r1       7      15789
data__r        8      100.0
data__r        9      0.000
data__r       10      0.000

edited May 17, 2012 at 15:50

answered May 17, 2012 at 15:40

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

7 Comments

eek Over a year ago

Will adding those irrelevant parts back still make this work? The reason I ask is because I want to directly reference the file.

marc_s Over a year ago

@eek142: yes, it will work with your complete XML, too - as long as you provide the proper definition for the ss: XML namespace prefix

eek Over a year ago

Isn't the XML namespace in this line: <x:WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">

marc_s Over a year ago

@eek142: that's the default XML namespace (without any prefix) - but that's only used in the scope of the <WorksheetOptions> node and its children

marc_s Over a year ago

@eek142: whatever is really in your file, yes - the part you showed starts at <Worksheet> - but if the real file actually has more nodes before that, those of course also have to be included in the XPath expression, yes.

|

Collectives™ on Stack Overflow

SQL Server 2008 OPENXML Insert

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related