open this xml example in sql server table (Network Steve Forum)

open this xml example in sql server table

I have an error on my statement, because resulset has not rows.

Can someone help me to identify my error?

Thanks in advance

DECLARE @handle INT
DECLARE @XML nvarchar(1000)
SET @XML = N'<html><head><style type="text/css"></style></head><body><data>
<timesheets><customerid>1</customerid><customername>Company 1</customername><billable>False</billable></timesheets>
</data>
</body></html>'

DECLARE @PrepareXmlStatus INT
EXEC @PrepareXmlStatus= sp_xml_preparedocument @handle OUTPUT, @XML

SELECT *
FROM OPENXML(@handle, '/body/data/timesheets')
WITH (
customerid INT,
customername varchar(50),
billable varchar(30)
)

June 18th, 2015 4:48pm

Hi,

Try this

DECLARE @handle INT 
 DECLARE @XML nvarchar(1000)
SET @XML = N'<html><head><style type="text/css"></style></head><body><data>
<timesheets><customerid>1</customerid><customername>Company 1</customername><billable>False</billable></timesheets>
</data>
</body></html>'

DECLARE @PrepareXmlStatus INT  
EXEC @PrepareXmlStatus= sp_xml_preparedocument @handle OUTPUT, @XML  

print @handle
print @XML

SELECT  *
FROM    OPENXML(@handle, '/html/body/data/timesheets',2)  
    WITH (
    customerid INT,
    customername varchar(50),
    billable varchar(30)
    )  

 go

Just a slight change - added /html to the xpath pattern and set the flags argument to 2 for element-centric mapping.

Proposed as answer by Olaf HelperMVP Friday, June 19, 2015 6:48 AM
Marked as answer by Jorge_lbs 15 hours 33 minutes ago

Free Windows Admin Tool Kit Click here and download it now

June 18th, 2015 7:02pm

Awesome

Thanks for your help

June 20th, 2015 11:41am

This topic is archived. No further replies will be accepted.