agrireeBlasia
New Member
I need some more help with another XML query. Below is an example of a record of my XML column:\[code\]<Fields> <MappedFields> <Field name="FormNumber" value="http://stackoverflow.com/questions/10388831/21" /> <Field name="ProcedureCode" value="http://stackoverflow.com/questions/10388831/T2023" /> <Field name="CurrentDate" value="http://stackoverflow.com/questions/10388831/4/23/2012" /> </MappedFields></Fields>\[/code\]The field elements may appear in any order, so it can appear like this as well:\[code\]<Fields> <MappedFields> <Field name="ProcedureCode" value="http://stackoverflow.com/questions/10388831/G5532" /> <Field name="FormNumber" value="http://stackoverflow.com/questions/10388831/12" /> <Field name="CurrentDate" value="http://stackoverflow.com/questions/10388831/3/29/2011" /> </MappedFields></Fields>\[/code\]What I am looking for, is a query that will get the value of the Field with the name "FormNumber" for all the records in the table. The query I have below works if the Field with the name of "FormNumber" is the first Field element in the XML. What I need is a query that will find the Field element even if it is not the first element. Can someone help me out with this?\[code\]SELECT X.Node.value(N'(Field/@value)[1]', 'nvarchar(max)') AS FormNumberFROM dbo.MHTCM_LetterSent ACROSS APPLY A.LetterXML.nodes(N'/Fields/MappedFields') AS X(Node)WHERE X.Node.value(N'(Field/@name)[1]', 'nvarchar(max)') = 'FormNumber'\[/code\]