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:





1
100
1
1
300
300

100






1
1
2


3


2


False
False












ID
Company Name
Item
Type
Total Time
Total Amount
Count Product
Percentage Time
Product Time
Invalid Product Time


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



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 = 

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

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 = '

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

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

SQL Server 2008 OPENXML Insert

Answers (1)

Related Questions