Need help in retrive the data from openxml.

Question

Need help in retrive the data from openxml.

panneermca35

Mr or Mrs. 500

Points: 504
More actions
July 15, 2014 at 2:52 am

#287907

Hi All,
Please read the following query.
DECLARE @MyXML XML
SET @MyXML = '<Item>
<Item accountnumber="900044010163" versionnumber="1" repaymentdate="2013-09-05" />
</Item>'
DECLARE @i INT
EXEC sp_xml_preparedocument @i OUTPUT, @MyXML
select * from OPENXML(@i, '/Item/Item') order by id
The Result:
idparentidnodetypelocalnameprefixnamespaceuridatatypeprevtext
201ItemNULLNULLNULLNULLNULL
322accountnumberNULLNULLNULLNULLNULL
422versionnumberNULLNULLNULLNULLNULL
522repaymentdateNULLNULLNULLNULLNULL
633#textNULLNULLNULLNULL900044010163
743#textNULLNULLNULLNULL1
853#textNULLNULLNULLNULL2013-09-05
But i want the result like
Accountnumber versionnumber repaymentdate
900044010163 1 '2013-09-05'
Because i had of different xml and each xml has x number of rows.
So kindly give me solution.
to get the all rows in table mode.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

panneermca35 (7/15/2014)
Hi All,
Please read the following query.
DECLARE @MyXML XML
SET @MyXML = '<Item>
<Item accountnumber="900044010163" versionnumber="1" repaymentdate="2013-09-05" />
</Item>'
DECLARE @i INT
EXEC sp_xml_preparedocument @i OUTPUT, @MyXML
select * from OPENXML(@i, '/Item/Item') order by id
The Result:
idparentidnodetypelocalnameprefixnamespaceuridatatypeprevtext
201ItemNULLNULLNULLNULLNULL
322accountnumberNULLNULLNULLNULLNULL
422versionnumberNULLNULLNULLNULLNULL
522repaymentdateNULLNULLNULLNULLNULL
633#textNULLNULLNULLNULL900044010163
743#textNULLNULLNULLNULL1
853#textNULLNULLNULLNULL2013-09-05
But i want the result like
Accountnumber versionnumber repaymentdate
900044010163 1 '2013-09-05'
Because i had of different xml and each xml has x number of rows.
So kindly give me solution.
to get the all rows in table mode.

Suggest you rather use the nodes method

😎

USE tempdb;

GO

DECLARE @MyXML XML

SET @MyXML = '<Item>

<Item accountnumber="900044010163" versionnumber="1" repaymentdate="2013-09-05" />

</Item>';

SELECT

IT.EM.value('@accountnumber','VARCHAR(12)') AS accountnumber

,IT.EM.value('@versionnumber','INT') AS versionnumber

,IT.EM.value('@repaymentdate','DATE') AS repaymentdate

FROM @MyXML.nodes('Item/Item') AS IT(EM)

Results

accountnumber versionnumber repaymentdate

------------- ------------- -------------

900044010163 1 2013-09-05

panneermca35 Mr or Mrs. 500 Points: 504 More actions · Answer 2

Hi Centuries,

Thanks for your reply.

At present we r using the same method.

Please give us the solution to get data in dynamically.