SqlServer XQuery always concat two fields

Question

I want to get somedata from one column only from an xml field:

<CDirData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://Fnet.ESB.Schemas.CentroDirectivo.CDirData">
  <ProcedureData xmlns="">
    <ProcedureId>7001</ProcedureId>
    <CentroDirectivo>Subsecretaria</CentroDirectivo>
  </ProcedureData>
  <SolicitudData xmlns="">
...

with my query

    SELECT  top 1
        [Message].query('//*[local-name()="ProcedureId"]').value('.','nvarchar(max)') as R
    from 
        ManagementCenterQueueHistorical

but allways returns the fields concat

enter image description here

I would need just only the first one

Thanks in advance!

score 1 · Accepted Answer · answered Feb 07 '14 at 13:10

Put your XPath expressin in parenthessis and add [1] to get only first element from the group

DECLARE @x XML = '<CDirData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://Fnet.ESB.Schemas.CentroDirectivo.CDirData">
  <ProcedureData xmlns="">
    <ProcedureId>7001</ProcedureId>
    <CentroDirectivo>Subsecretaria1</CentroDirectivo>
  </ProcedureData>
  <ProcedureData xmlns="">
    <ProcedureId>7002</ProcedureId>
    <CentroDirectivo>Subsecretaria2</CentroDirectivo>
  </ProcedureData>
  <ProcedureData xmlns="">
    <ProcedureId>7003</ProcedureId>
    <CentroDirectivo>Subsecretaria3</CentroDirectivo>
  </ProcedureData>
  <ProcedureData xmlns="">
    <ProcedureId>7004</ProcedureId>
    <CentroDirectivo>Subsecretaria4</CentroDirectivo>
  </ProcedureData>
  </CDirData>'

SELECT
    @x.query('(//*[local-name()="ProcedureId"])[1]').value('.','nvarchar(max)') as R

SqlServer XQuery always concat two fields

1 Answers1