Problems quering XML

• MrAkki

  SSCrazy

  Points: 2574

  More actions

  March 27, 2017 at 9:14 am

  #323246

  Hi there,
  I have an XML with the following structure (I also attached the xml as file):
  
<SSIS:Parameters xmlns:SSIS="www.microsoft.com/SqlServer/SSIS">
<SSIS:Parameter SSIS:Name="AccountID">
  <SSIS:Properties>
  <SSIS:Property SSIS:Name="ID">{736c5778-eb62-4227-8f1f-da3bb23be0b8}</SSIS:Property>
  <SSIS:Property SSIS:Name="CreationName" />
  <SSIS:Property SSIS:Name="Description">Account ID</SSIS:Property>
  <SSIS:Property SSIS:Name="IncludeInDebugDump">0</SSIS:Property>
  <SSIS:Property SSIS:Name="Required">1</SSIS:Property>
  <SSIS:Property SSIS:Name="Sensitive">0</SSIS:Property>
  <SSIS:Property SSIS:Name="Value">ACC12345678</SSIS:Property>
  <SSIS:Property SSIS:Name="DataType">18</SSIS:Property>
  </SSIS:Properties>
</SSIS:Parameter>
<SSIS:Parameter SSIS:Name="QueryKey">
  <SSIS:Properties>
  <SSIS:Property SSIS:Name="ID">{11c138a0-6a8c-4507-b746-14cdc9344fbf}</SSIS:Property>
  <SSIS:Property SSIS:Name="CreationName" />
  <SSIS:Property SSIS:Name="Description">Key</SSIS:Property>
  <SSIS:Property SSIS:Name="IncludeInDebugDump">0</SSIS:Property>
  <SSIS:Property SSIS:Name="Required">1</SSIS:Property>
  <SSIS:Property SSIS:Name="Sensitive">0</SSIS:Property>
  <SSIS:Property SSIS:Name="Value">hjdhfhuienrfuuejj837jjam</SSIS:Property>
  <SSIS:Property SSIS:Name="DataType">18</SSIS:Property>
  </SSIS:Properties>
</SSIS:Parameter>
<SSIS:Parameter SSIS:Name="DateTime">
  <SSIS:Properties>
  <SSIS:Property SSIS:Name="ID">{631d01ab-4fcc-4128-a08b-ae1d7f13e87e}</SSIS:Property>
  <SSIS:Property SSIS:Name="CreationName" />
  <SSIS:Property SSIS:Name="Description" />
  <SSIS:Property SSIS:Name="IncludeInDebugDump">0</SSIS:Property>
  <SSIS:Property SSIS:Name="Required">0</SSIS:Property>
  <SSIS:Property SSIS:Name="Sensitive">0</SSIS:Property>
  <SSIS:Property SSIS:Name="Value">2017-03-24T15:55:12</SSIS:Property>
  <SSIS:Property SSIS:Name="DataType">16</SSIS:Property>
  </SSIS:Properties>
</SSIS:Parameter>
</SSIS:Parameters>


  I tried to query the xml but I cannot get it to work.
  Has anybody an idea how the SELECT statement has to look like?

  Thank you very much in advance for you help.

  Kind Regards,
  MrAkki

• MrAkki

  SSCrazy

  Points: 2574

  More actions

  March 27, 2017 at 9:14 am

  #1935102

  MrAkki - Monday, March 27, 2017 9:14 AM

  Hi there,
  I have an XML with the following structure (I also attached the xml as file):
  
<SSIS:Parameters xmlns:SSIS="www.microsoft.com/SqlServer/SSIS">
<SSIS:Parameter SSIS:Name="AccountID">
  <SSIS:Properties>
  <SSIS:Property SSIS:Name="ID">{736c5778-eb62-4227-8f1f-da3bb23be0b8}</SSIS:Property>
  <SSIS:Property SSIS:Name="CreationName" />
  <SSIS:Property SSIS:Name="Description">Account ID</SSIS:Property>
  <SSIS:Property SSIS:Name="IncludeInDebugDump">0</SSIS:Property>
  <SSIS:Property SSIS:Name="Required">1</SSIS:Property>
  <SSIS:Property SSIS:Name="Sensitive">0</SSIS:Property>
  <SSIS:Property SSIS:Name="Value">ACC12345678</SSIS:Property>
  <SSIS:Property SSIS:Name="DataType">18</SSIS:Property>
  </SSIS:Properties>
</SSIS:Parameter>
<SSIS:Parameter SSIS:Name="QueryKey">
  <SSIS:Properties>
  <SSIS:Property SSIS:Name="ID">{11c138a0-6a8c-4507-b746-14cdc9344fbf}</SSIS:Property>
  <SSIS:Property SSIS:Name="CreationName" />
  <SSIS:Property SSIS:Name="Description">Key</SSIS:Property>
  <SSIS:Property SSIS:Name="IncludeInDebugDump">0</SSIS:Property>
  <SSIS:Property SSIS:Name="Required">1</SSIS:Property>
  <SSIS:Property SSIS:Name="Sensitive">0</SSIS:Property>
  <SSIS:Property SSIS:Name="Value">hjdhfhuienrfuuejj837jjam</SSIS:Property>
  <SSIS:Property SSIS:Name="DataType">18</SSIS:Property>
  </SSIS:Properties>
</SSIS:Parameter>
<SSIS:Parameter SSIS:Name="DateTime">
  <SSIS:Properties>
  <SSIS:Property SSIS:Name="ID">{631d01ab-4fcc-4128-a08b-ae1d7f13e87e}</SSIS:Property>
  <SSIS:Property SSIS:Name="CreationName" />
  <SSIS:Property SSIS:Name="Description" />
  <SSIS:Property SSIS:Name="IncludeInDebugDump">0</SSIS:Property>
  <SSIS:Property SSIS:Name="Required">0</SSIS:Property>
  <SSIS:Property SSIS:Name="Sensitive">0</SSIS:Property>
  <SSIS:Property SSIS:Name="Value">2017-03-24T15:55:12</SSIS:Property>
  <SSIS:Property SSIS:Name="DataType">16</SSIS:Property>
  </SSIS:Properties>
</SSIS:Parameter>
</SSIS:Parameters>


  I tried to query the xml but I cannot get it to work.
  Has anybody an idea how the SELECT statement has to look like?

  Thank you very much in advance for you help.

  Kind Regards,
  MrAkki

  The smilies should be : ....

• MrAkki

  SSCrazy

  Points: 2574

  More actions

  March 27, 2017 at 9:15 am

  #1935103

  MrAkki - Monday, March 27, 2017 9:14 AM

  MrAkki - Monday, March 27, 2017 9:14 AM

  Hi there,
  I have an XML with the following structure (I also attached the xml as file):
  
<SSIS:Parameters xmlns:SSIS="www.microsoft.com/SqlServer/SSIS">
<SSIS:Parameter SSIS:Name="AccountID">
  <SSIS:Properties>
  <SSIS:Property SSIS:Name="ID">{736c5778-eb62-4227-8f1f-da3bb23be0b8}</SSIS:Property>
  <SSIS:Property SSIS:Name="CreationName" />
  <SSIS:Property SSIS:Name="Description">Account ID</SSIS:Property>
  <SSIS:Property SSIS:Name="IncludeInDebugDump">0</SSIS:Property>
  <SSIS:Property SSIS:Name="Required">1</SSIS:Property>
  <SSIS:Property SSIS:Name="Sensitive">0</SSIS:Property>
  <SSIS:Property SSIS:Name="Value">ACC12345678</SSIS:Property>
  <SSIS:Property SSIS:Name="DataType">18</SSIS:Property>
  </SSIS:Properties>
</SSIS:Parameter>
<SSIS:Parameter SSIS:Name="QueryKey">
  <SSIS:Properties>
  <SSIS:Property SSIS:Name="ID">{11c138a0-6a8c-4507-b746-14cdc9344fbf}</SSIS:Property>
  <SSIS:Property SSIS:Name="CreationName" />
  <SSIS:Property SSIS:Name="Description">Key</SSIS:Property>
  <SSIS:Property SSIS:Name="IncludeInDebugDump">0</SSIS:Property>
  <SSIS:Property SSIS:Name="Required">1</SSIS:Property>
  <SSIS:Property SSIS:Name="Sensitive">0</SSIS:Property>
  <SSIS:Property SSIS:Name="Value">hjdhfhuienrfuuejj837jjam</SSIS:Property>
  <SSIS:Property SSIS:Name="DataType">18</SSIS:Property>
  </SSIS:Properties>
</SSIS:Parameter>
<SSIS:Parameter SSIS:Name="DateTime">
  <SSIS:Properties>
  <SSIS:Property SSIS:Name="ID">{631d01ab-4fcc-4128-a08b-ae1d7f13e87e}</SSIS:Property>
  <SSIS:Property SSIS:Name="CreationName" />
  <SSIS:Property SSIS:Name="Description" />
  <SSIS:Property SSIS:Name="IncludeInDebugDump">0</SSIS:Property>
  <SSIS:Property SSIS:Name="Required">0</SSIS:Property>
  <SSIS:Property SSIS:Name="Sensitive">0</SSIS:Property>
  <SSIS:Property SSIS:Name="Value">2017-03-24T15:55:12</SSIS:Property>
  <SSIS:Property SSIS:Name="DataType">16</SSIS:Property>
  </SSIS:Properties>
</SSIS:Parameter>
</SSIS:Parameters>


  I tried to query the xml but I cannot get it to work.
  Has anybody an idea how the SELECT statement has to look like?

  Thank you very much in advance for you help.

  Kind Regards,
  MrAkki

  The smilies should be : ....

  I mean : and a P with out

• Thom A

  SSC Guru

  Points: 99127

  More actions

  March 27, 2017 at 9:18 am

  #1935105

  What information are you trying to get out of the xml? What do you want your output data to look like?

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Eirikur Eiriksson

  SSC Guru

  Points: 182901

  More actions

  March 27, 2017 at 9:27 am

  #1935110

  This might get you started
  😎
  
SELECT
  PROP.DATA.value('local-name(../.)','VARCHAR(100)') AS Parent_element
 ,PROP.DATA.value('local-name(.)','VARCHAR(100)')  AS Element
 ,PROP.DATA.value('(@*)[1]','VARCHAR(100)')    AS Parameter_Value
FROM @TXML.nodes('//*:Property') PROP(DATA)
;

• MrAkki

  SSCrazy

  Points: 2574

  More actions

  March 28, 2017 at 12:48 am

  #1935202

  Thom A - Monday, March 27, 2017 9:18 AM

  What information are you trying to get out of the xml? What do you want your output data to look like?

  I need to extract all the data like Name and Value (Account-ID: ACC12345678 and the DataType:18).

  (I did encapsulate my code in the IF code markup... still getting the emoticon.)

• MrAkki

  SSCrazy

  Points: 2574

  More actions

  March 28, 2017 at 12:56 am

  #1935203

  Eirikur Eiriksson - Monday, March 27, 2017 9:27 AM

  This might get you started
  😎
  
SELECT
  PROP.DATA.value('local-name(../.)','VARCHAR(100)') AS Parent_element
 ,PROP.DATA.value('local-name(.)','VARCHAR(100)')  AS Element
 ,PROP.DATA.value('(@*)[1]','VARCHAR(100)')    AS Parameter_Value
FROM @TXML.nodes('//*:Property') PROP(DATA)
;

  Thanks, But when I try it I get an error:
  XQuery [nodes()]: No more tokens expected at the end of the XQuery expression. Found 'Property'.

  Any idea?

• Eirikur Eiriksson

  SSC Guru

  Points: 182901

  More actions

  March 28, 2017 at 1:05 am

  #1935204

  MrAkki - Tuesday, March 28, 2017 12:56 AM

  Eirikur Eiriksson - Monday, March 27, 2017 9:27 AM

  This might get you started
  😎
  
SELECT
  PROP.DATA.value('local-name(../.)','VARCHAR(100)') AS Parent_element
 ,PROP.DATA.value('local-name(.)','VARCHAR(100)')  AS Element
 ,PROP.DATA.value('(@*)[1]','VARCHAR(100)')    AS Parameter_Value
FROM @TXML.nodes('//*:Property') PROP(DATA)
;

  Thanks, But when I try it I get an error:
  XQuery [nodes()]: No more tokens expected at the end of the XQuery expression. Found 'Property'.

  Any idea?

  Change that emojo to a colon and a capital P respectfully
  😎

  If you hit the "Quote" button, you can copy the code without the emojo's