Eirikur Eiriksson - Tuesday, January 24, 2017 6:46 AM

rash3554 - Tuesday, January 24, 2017 4:11 AM

Thanks.
But this prints only valueA from <Property Name="paramB" Value="valueA" />

and not valueY from
<Property Name="paramB" Value="valueY" />
How do I iterate through all Property elements?

Thanks
Rash

Change the XPath to iterate through the Property nodes
😎

DECLARE @TTXML TABLE (TXML XML);
INSERT INTO @TTXML(TXML) VALUES( '<?xml version="1.0" encoding="utf-8"?><container>
  <Property Name="paramA" Vocabulary="someVocu">
    <Property Name="paramB" Value="valueA" />
    <Property Name="paramC" Value="valueB" />
  </Property>
  <Property Name="paramA" Vocabulary="anotherVocu">
    <Property Name="paramB" Value="valueY" />
    <Property Name="paramC" Value="valueZ" />
  </Property>
</container>');

SELECT
    PROP.DATA.value('@Name' ,'VARCHAR(50)') AS PARAM_NAME
   ,PROP.DATA.value('@Value','VARCHAR(50)') AS PARAM_VALUE
FROM @TTXML TX
CROSS APPLY TX.TXML.nodes('/container/Property/Property[@Name="paramB"]') AS PROP(DATA);='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>