Reading xml problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Reading xml problem

Author

Topic

moodi_z
Starting Member

38 Posts

Posted - 2009-05-21 : 01:52:31

Hi,

I have this xml file:
<POInfo>
<PO>
<POID>111</POID>
<LineID>P1</LineID>
<CatalogID>J001</CatalogID>
<RequestedQty>100.00000</RequestedQty>
<Status>Created</Status>
<LotID/>
<Priority/>
<Created>20/05/2009</Created>
<AvailTime/>
<StartTime/>
<EndTime/>
<UserID>Administrator</UserID>
<EndUserID/>
</PO>

<POTags>
<Tag ID="Test1">
<Value>10</Value>
</Tag>
<Tag ID="Test2">
<Value>20</Value>
</Tag>
</POTags>

</POInfo>

and in my stored procedure I wrote:
Alter PROCEDURE sp_InsertPODetails
@XMLdoc AS varchar(1000)
--@XMLdoc AS ntext
--@XMLdoc AS xml
AS
BEGIN
SET NOCOUNT ON;
DECLARE @FileHandle int

EXEC sp_xml_preparedocument @FileHandle OUTPUT, @XMLdoc

Select ID, [Value]
FROM OPENXML (@FileHandle, 'POInfo/POTags')
WITH (ID Varchar(10), Value varchar(10) 'Value')

but i'm getting nulls!!

By the way, can I pass the xml file as a parameter and not it's content?!

Thanks in advance.

mualsh
Starting Member

8 Posts

Posted - 2009-05-21 : 08:29:04

The default value for the flag parameter to the OPENXML statement is "attribute-centric" mapping. And since the POTags node in your XML does not have any attributes associated with it, you are ending up with NULL values.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-22 : 04:23:59

did you try like this?


Select *
FROM OPENXML (@FileHandle, '/POInfo/POTags/Tag')
WITH (ID Varchar(10) './@ID', 
Value varchar(10) './Value')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources