Yestedday James K kindly helped out with my query reading XML from a text field when the XML file has a namespace ( below ) WITH XMLNAMESPACES (DEFAULT 'http://bacs.co.uk/submissions')SELECT t.u.value('(./@status)[1]','varchar(20)') AS Result, t.u.value('(./@index)[1]','int') AS FileIndex, t.u.value('(./@paymentFileIdentifier)[1]','varchar(6)') AS FileSerialNumber, t.u.value('(./@processingDay)[1]','varchar(10)') AS ProcessingDate , t.u.value('(./@creditRecordCount)[1]','int') AS CreditCount, t.u.value('(./@creditValueTotal)[1]','varchar(11)') AS CreditTotal, t.u.value('(./@debitRecordCount)[1]','int') AS DebitCount, t.u.value('(./@debitValueTotal)[1]','varchar(11)') AS DebitTotal, t.u.value('(./@ddiRecordCount)[1]','int') AS AUDDISCount, t.u.value('(./OriginatingServiceUser/@userNumber)[1]','varchar(6)') AS SUN, t.u.value('(./OriginatingServiceUser/@name)[1]','varchar(18)') AS OAccountName, t.u.value('(../SigningContact/@fullName)[1]','varchar(30)') AS FileSignedBy, t.u.value('(../SubmittingContact/@fullName)[1]','varchar(30)') AS FileSubmittedBy, UPPER(t.u.value('(../@submissionType)[1]','varchar(30)')) AS SubmissionType, t.u.value('(../@submissionSerialNumber)[1]','varchar(30)') AS SubmissionSerialNumber, t.u.value('(../@submissionIdentifier)[1]','varchar(100)') AS BacsSubmissionIdentifierFROM ( SELECT CAST(CAST(SubmissionResults AS NTEXT) AS XML) AS Xmlreport FROM [TESTDB].[dbo].[TESTTABLE] WHERE SerialNumber = 9 ) dCROSS APPLY Xmlreport.nodes('/SubmissionResults/PaymentFile')t(u)

I have now run across a similar problem and have spend hours trying to resolve it. n this instance i am just trying to recover the time as a varchar although I will need to expand the query. I just need to get a start point ie a working piece of code

SELECT t.a.value('(@time)[1]', 'varchar(10)') as 'time'

FROM (SELECT CAST(CAST(XMLText AS NTEXT) AS XML) AS Xmlreport FROM [TESTDB].[dbo].[TESTTABLE] WHERE [Id] = '56561407') d CROSS APPLY Xmlreport.nodes('//Data/InputReport/Header/ProducedOn') t(a)

Data from the XMLText field. I have layed it out in here to make it easier to read

The way I do this is to first format the XML nicely so you can visually see how the XML looks like.

The First method is probably NOT what you want. I wrote that there only to show what it is doing. If you look in the FROM clause of that query, it has a '//ProducedOn'. What that says is, "Go down the tree and find the "ProducedOn" nodes, no matter where in the tree they arey. Then, the select clause says, give me the time attribute of that node.

Note that I don't even have the indexer (as in (@time)[1]) there. That is because a node can have only one attribute with a given name, so there is no need to pick one.

The second method is giving an exact path. It says travel down to BACSDocument (in the FROM clause), and then from there, navigate down to Data/InputReport/Header/ProducedOn. There could be multiple nodes that satisfy this condition, and value function requires one node that it can work with. Hence the (Data/InputReport/Header/ProducedOn)[1] indexer. Then, once you are there, find the time attribute.

The third would produce the same result in this case, but if you had multiple Data nodes, it would produce one row for each Data node. Because in this case, FROM clause takes you down to Data node.

Fourth is another variation. Add couple more Data nodes, Header nodes etc. and you will see how each differs

I didn't mean to suggest that you should format the complete XML file. Pick a small representative sample and understand that, so you know what the layout is. You can also generate the schema from the XML or even ask the people who produced the XML if they have the schema, so you can understand how the data is structured.

If the data is in a table, instead of "FROM @x.nodes('/BACSDocument') T(a)", you would use "FROM YourTableName a CROSS APPLY a.XMLColumn.nodes('/BACSDocument') T(a) ". If the column is not XML type, you would need to cast it just like you had in the example you posted above.