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.
| Author |
Topic |
|
Pete_N
Posting Yak Master
181 Posts |
 Posted - 2014-06-26 : 10:10:26
|
| Im trying to get the value of TotalNumberOfNewAdvices but can't remember how. can any one help<VocaDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="VOCALINK_DDICAdvice.xsd"> <Data> <Document > <CompanyName>Bacs Payment Schemes Limited</CompanyName> <ReportTitle>DIRECT DEBIT INDEMNITY CLAIM ADVICE REPORT FOR 24/06/2014</ReportTitle> <ReportProductionDate>2014-06-25T00:28:20</ReportProductionDate> <NumberOfAdvices>1</NumberOfAdvices> <NewAdvices> <DDICAdvice> <NoOfAdvForClaim>1</NoOfAdvForClaim> </DDICAdvice> <TotalNumberOfNewAdvices>1</TotalNumberOfNewAdvices> </NewAdvices> </Document> </Data></VocaDocument>The xml has been loaded into a temp table #WORKINGXML SELECT j.c.value('TotalNumberOfNewAdvices[1]' , 'varchar(99)') AS Counts FROM ( SELECT XMLTEXT AS Xmlreport FROM #WORKINGXML) d CROSS APPLY Xmlreport.nodes('/VocaDocument/Data/Document') AS a(c) OUTER APPLY a.c.nodes('/NewAdvices') j(c) |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-06-26 : 10:37:32
|
Is XMLTEXT column of data type XML? If not you should cast it to XML. Also, Remove the slash from the last line....OUTER APPLY a.c.nodes('NewAdvices') j(c) |
 |
|
|
Pete_N
Posting Yak Master
181 Posts |
Posted - 2014-06-26 : 10:47:53
|
quote:
Originally posted by James K
Is XMLTEXT column of data type XML? If not you should cast it to XML. Also, Remove the slash from the last line....OUTER APPLY a.c.nodes('NewAdvices') j(c)
Gotcha, thanks , its all coming back now |
|
|
|
|
|
|
Brain Freeze reading XML