OpenXML Results

I have an OpenXML query:
DECLARE @.hDoc int
EXEC sp_xml_preparedocument @.hDoc output,
'<query>
<games>
<gameid>20200542</gameid>
<gameid>20200180</gameid>
<gameid>20200169</gameid>
</games>
</query>
'
SELECT * FROM OPENXML(@.hdoc, '/query/games/gameid', 2)
WITH (gameid varchar(80) '../gameid')
EXEC sp_xml_removedocument @.hDoc
It returns 3 records but repeats gamid 20200542 three times.
What I want is 3 records one with each GameId. What am I doing wrong?
Try this instead:
DECLARE @.hDoc int
EXEC sp_xml_preparedocument @.hDoc output,
'<query>
<games>
<gameid>20200542</gameid>
<gameid>20200180</gameid>
<gameid>20200169</gameid>
</games>
</query>
'
SELECT * FROM OPENXML(@.hdoc, '/query/games/gameid', 2)
WITH (gameid varchar(80) '.')
EXEC sp_xml_removedocument @.hDoc
"Joe LeBaron" <Joe@.Spam.com> wrote in message
news:eD4vjeAnEHA.2948@.TK2MSFTNGP11.phx.gbl...
> I have an OpenXML query:
> DECLARE @.hDoc int
> EXEC sp_xml_preparedocument @.hDoc output,
> '<query>
> <games>
> <gameid>20200542</gameid>
> <gameid>20200180</gameid>
> <gameid>20200169</gameid>
> </games>
> </query>
> '
> SELECT * FROM OPENXML(@.hdoc, '/query/games/gameid', 2)
> WITH (gameid varchar(80) '../gameid')
> EXEC sp_xml_removedocument @.hDoc
> It returns 3 records but repeats gamid 20200542 three times.
> What I want is 3 records one with each GameId. What am I doing wrong?
>
|||Thanks... I was trying every combination of /'s and .'s. Makes perfect
sense (now).
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23VNHXiAnEHA.3712@.TK2MSFTNGP15.phx.gbl...
> Try this instead:
> DECLARE @.hDoc int
> EXEC sp_xml_preparedocument @.hDoc output,
> '<query>
> <games>
> <gameid>20200542</gameid>
> <gameid>20200180</gameid>
> <gameid>20200169</gameid>
> </games>
> </query>
> '
> SELECT * FROM OPENXML(@.hdoc, '/query/games/gameid', 2)
> WITH (gameid varchar(80) '.')
> EXEC sp_xml_removedocument @.hDoc
>
>
> "Joe LeBaron" <Joe@.Spam.com> wrote in message
> news:eD4vjeAnEHA.2948@.TK2MSFTNGP11.phx.gbl...
>

OPENXML question

Hi!

I am trying to import an xml file into a SQL 2005 table using sp_xml_prepareDocument ...OPENXML.

I always get 0 rows affected even though there is data in the file. The table structure is identical to the XML output. The OpenXML qry uses the following syntax:

FROM OPENXML(@.xmlHndAdd, '/NewDataSet/Table1', 1)
WITH MyTbl

The XML file format is:

<NewDataSet xmlns="">
<Table1 diffgr:id="Table11" msdata:rowOrder="0">
<program_id>1-2-3-4-5</program_id>
<object_name />
</Table1>
<Table1 diffgr:id="Table12" msdata:rowOrder="1">
<object_id>6-7-8-9-0</object_id>
<object_name>ABC</object_name>
<objectproperty_id>1-3-5-7-9</objectproperty_id>
</Table1>

Any suggestions are greatly appreciated!!!

Thank you!

Found the right syntax if anyone has similar questions:

DECLARE @.xmlHndAdd INT

EXEC sp_xml_prepareDocument @.xmlHndAdd OUTPUT, @.availabilityXml

TRUNCATE TABLE mytbl

INSERT mytbl

SELECT *

FROM OPENXML(@.xmlHndAdd, '//NewDataSet/Table1', 2)

WITH mytbl

OPENXML error

Hi,
I have an SP which deals with xml. The SP receives an ntext argument that
has the xml string.
The sp_xml_preparedocument sp is called to get a handle from the ntext.
Then an OPENXML statement clubbed with an INSERT statement is executed.
Example :
INSERT INTO dpcmain.ProcessedSecurity ( FileId, SecurityId, RecordDate,
UnprocessedData )
SELECT @.FileId, SecurityId, RecordDate, UnprocessedData
FROM OPENXML (@.PSDoc,'xml/rs:data/rs:insert/z:row',0)
WITH (SecurityID integer, RecordDate varchar(128), UnprocessedData
varchar(256))
where @.PSDoc is the handle to the xml document.
We occasionally get an error in the OPENXML statement.That is okay, however,
the problem is that SQL Server DOES NOT go to the next line in the SP. In
the next line I check for @.@.error and take some action if OPENXML fails.
Is it a known issue that when OPENXML fails, the control goes out of the SP
and DOES NOT go to the next line in the SP?
Thanks in advance,
Bhasker.
Hi,
Whether or not a Stored Procedure or Trigger continues onto the next line, following an error is dependant on the type of error that is raised. Back in the days of 6.5, this did NOT depend on the Severity of the error, but I'm not sure if that has changed
with 2000.
So, the actual error that you are getting would be useful.

OPENXML error

Hi,
I have an SP which deals with xml. The SP receives an ntext argument that
has the xml string.
The sp_xml_preparedocument sp is called to get a handle from the ntext.
Then an OPENXML statement clubbed with an INSERT statement is executed.
Example :
INSERT INTO dpcmain.ProcessedSecurity ( FileId, SecurityId, RecordDate,
UnprocessedData )
SELECT @.FileId, SecurityId, RecordDate, UnprocessedData
FROM OPENXML (@.PSDoc,'xml/rs:data/rs:insert/z:row',0)
WITH (SecurityID integer, RecordDate varchar(128), UnprocessedData
varchar(256))
where @.PSDoc is the handle to the xml document.
We occasionally get an error in the OPENXML statement.That is okay, however,
the problem is that SQL Server DOES NOT go to the next line in the SP. In
the next line I check for @.@.error and take some action if OPENXML fails.
Is it a known issue that when OPENXML fails, the control goes out of the SP
and DOES NOT go to the next line in the SP?
Thanks in advance,
Bhasker.Hi,
Whether or not a Stored Procedure or Trigger continues onto the next line, f
ollowing an error is dependant on the type of error that is raised. Back in
the days of 6.5, this did NOT depend on the Severity of the error, but I'm n
ot sure if that has changed
with 2000.
So, the actual error that you are getting would be useful.