Problems quering XML

  • 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 - 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 - 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

  • 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

  • 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)
    ;

  • 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.)

  • 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?

  • 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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply