[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

How to query a CLOB field

Hi.  I have a piece of code that downloads a Clob field into a table and then I parse out the node that I need from the field.  The problem is that there are several nodes that have the same name and I need to be able to filter by the attribute.  I have attached my code as well as a snapshot of what the node tree looks like.  The name of the nodes are all called "item" but they are all have different attribute types.  I am looking for attribute name =  'Type'  and attribute value =  "TX_SALE" but I don't know how to write this in the where statement.  I have selected the first node in my where statement by default but this isn't returning all my data.  I need to be able to filter by attribute.  Can someone help me?
INSERT into #WorkingDataset
SELECT
	CAST(xmlsrc.SITE_NO as Integer)			SITE_NO,
	CAST(xmlsrc.TRAN_DT as DateTime)		TRAN_DT, 
	CAST(xmlsrc.REG_NO as Integer)			REG_NO,
	CAST(xmlsrc.TRAN_NO as Integer)			TRAN_NO,
	CAST(xmlsrc.XML_PENDING as CHAR(1))		XML_Status,
	CAST(xmlsrc.XML_DATA as XML) 			XML_DATA
	
FROM
	[TOMAX_PROD]..[TMX].[TX_STAGING] xmlsrc
	
-----------------------------------------------------------
-- Now that we converted this to xml data we can use,
-- we need to return everything that has a reason_code
-----------------------------------------------------------
SELECT
	Site_No,
	Tran_dt,
	Reg_no,
	Tran_No,
	XML_Status,
	nref.value('itemno[1]', 'VarChar(12)') SKUCode,
	nref.value('quantity[1]', 'Integer') QuantitySold,
	nref.value('reason_code[1]/qty[1]', 'Integer') qty
INTO #ReturnResults	
FROM   
	#WorkingDataset CROSS APPLY XML_Data.nodes('/transaction/item') AS R(nref)
WHERE
	LEN(nref.value('itemno[1]', 'VarChar(12)')) = 6
	
ORDER BY
	Site_No,
	Tran_dt,
	Reg_no,
	Tran_No

Open in new window

0
skielark
Asked:
skielark
  • 3
  • 2
4 Solutions
 
Mark WillsTopic AdvisorCommented:
Can you the XML source data ? just a few... Think you are missing that snapshot as well...
0
 
skielarkAuthor Commented:
I don't even understand the question.  Can you the XML source data?  What does that mean.
0
 
Mark WillsTopic AdvisorCommented:
Yeah - I am having a hard time understanding it myself ...

CLOB returns the contents as a single-row, single-column rowset of type varchar(max), so you may have to use some of the XML facilities.

Can you post a few lines of of your source data... and how you are loading it ?

You mentioned a snapshot that you were attaching, but didn't see it...
0
 
Mark WillsTopic AdvisorCommented:
Also, what versions of SQL are you using...
0
 
skielarkAuthor Commented:
Mark, I can use either SQL server 2000 or 2005.  I attached the screenshot of the source data with the nodes.
Attribute.doc
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now