SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query with Xml
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pushp82
Yak Posting Veteran

83 Posts

Posted - 03/27/2014 :  02:55:23  Show Profile  Reply with Quote
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

Japan
68 Posts

Posted - 03/27/2014 :  03:52:00  Show Profile  Reply with Quote
SELECT
	BID_ID,
	node.value('.', 'varchar(100)') ODR_Number
FROM <YOUR_TABLE_NAME>
CROSS APPLY BID_Detail.nodes('//ODR_Number') as nodelist(node)


-------------------------------------
From Japan
Sorry, my English ability is limited.
Go to Top of Page

pushp82
Yak Posting Veteran

83 Posts

Posted - 03/31/2014 :  07:15:08  Show Profile  Reply with Quote
thanks nagino, I had approched the same way that why missed to thanks you same day.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000