Revised Question XML Value Method Return Multiple Rows

Question

Question earlier asked and answered to run query to collect sql data from xml schema

was able to collect the scheme from node but query did not return data but xml string

question

I ran this query

select x.col.value ('

 

 

FROM PATIENTTRAC_SUBMISSION_HISTORY

cross apply

PATIENTTRAC_EDI_SUBMISSION.nodes('

(text())[1]', 'varchar(max)') as outNode

 

 

//CLAIM_x0020_Patient_x0020_Account_x0020_Number') x(col)

Get this error:

Msg 9312, Level 16, State 1, Line 3

XQuery [PATIENTTRAC_SUBMISSION_HISTORY.PATIENTTRAC_EDI_SUBMISSION.value()]: 'text()' is not supported on simple typed or 'http://www.w3.org/2001/XMLSchema#anyType' elements, found 'element(CLAIM_x0020_Patient_x0020_Account_x0020_Number,xs:string)'.

Trying to pull data from schema from large EDI 837 xml files

 

thanks

 

wayne

 

---

---

H Wayne Hayes, Jr.

Answer 1

Hi,

Take a look at the following Connect issue:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=590534&wa=wsignin1.0

Thanks,

Cathy Miller

Microsoft Online Community Support

 

Answer 2

Thanks

I appreciate the link, i ended up using another table to move the query data into rows and then

ran the .value against the second table 

 

wayne

---

H Wayne Hayes, Jr.