Query with Xml - 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

General SQL Server Forums

New to SQL Server Programming

Query with Xml

Author

Topic

pushp82
Yak Posting Veteran

83 Posts

Posted - 2014-03-27 : 02:55:23

Hi,

I have table called tbl_detail which has columns like:
BID_ID, BID_Detail

BID_ID is a PK and each BID_Detail contains a XML as below

<DocumentElement>
<Data>
<ODR_Date>2012-11-26T17:18:53.2+05:30</ODR_Date>
<ODR_Number>NT061126201211535</ODR_Number>
<ODR_NONTAX_AMT>210.00</ODR_NONTAX_AMT>
</Data>
<Data>
<ODR_Date>2012-11-09T21:31:53.673+05:30</ODR_Date>
<ODR_Number>NT06119201211523</ODR_Number>
<ODR_NONTAX_AMT>205.00</ODR_NONTAX_AMT>
</Data>
<Data>
<ODR_Date>2012-10-28T20:34:19.907+05:30</ODR_Date>
<ODR_Number>NT061028201211517</ODR_Number>
<ODR_NONTAX_AMT>200.00</ODR_NONTAX_AMT>
</Data>
<Data>
<ODR_Date>2012-09-21T16:39:12.127+05:30</ODR_Date>
<ODR_Number>NT06921201211458</ODR_Number>
<ODR_NONTAX_AMT>620.00</ODR_NONTAX_AMT>
</Data>
</DocumentElement>

I need to fetch all ODR_Number from each xml, like:

BID_ID ODR_Number
1 NT061126201211535
1 NT06119201211523
1 NT061028201211517
1 NT06921201211458
2 --- number's from next xml
2 ----

Please help me asap.

Thanks,

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-03-27 : 03:52:00

[code]SELECT
BID_ID,
node.value('.', 'varchar(100)') ODR_Number
FROM <YOUR_TABLE_NAME>
CROSS APPLY BID_Detail.nodes('//ODR_Number') as nodelist(node)
[/code]

-------------------------------------
From Japan
Sorry, my English ability is limited.

pushp82
Yak Posting Veteran

83 Posts

Posted - 2014-03-31 : 07:15:08

thanks nagino, I had approched the same way that why missed to thanks you same day.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources