Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SELECT FROM OpenXML not returning any data Expand / Collapse
Author
Message
Posted Saturday, May 25, 2013 10:18 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 4:36 AM
Points: 307, Visits: 235
I have the following challenge.
I would like to parse XML data into a relational table.

<NSTRoot xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://volvo.com/vss/4_0/v1_0">
<Header>
<General>
<MsgID>2d981758-3718-4ea0-8740-1f2faa788c2c</MsgID>
<TimeStamp>2013-04-24T07:27:53.7190466Z</TimeStamp>
</General>
</NSTRoot>

This is just a small part of the XML File.

I am using the following statement:
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XML = XMLData FROM tblTQ

select @XML

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML


SELECT MsgID,TimeStamp
FROM OPENXML(@hDoc, '/Header/General')
WITH
(
MsgID Uniqueidentifier '@MsgID',
TimeStamp datetime '@TimeStamp'

)


EXEC sp_xml_removedocument @hDoc
GO

There are no results What am i doing wrong?


Post #1456839
Posted Monday, May 27, 2013 3:23 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 6:57 AM
Points: 982, Visits: 1,083
Hi,

I think you must insert the namespace near
FROM OPENXML(@hDoc, '/Header/General')


Jon
Post #1457020
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse