Problems querying XML in SQL Server 2014

Question

I have an XML with the following structure:

    <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 try to use a SQL Server SELECT statement but I cannot get it to work. Do you have any idea how to select the data? I need to get the Name like 'AccountID' and the Value 'ACC12345678' and the DataType '18' in one row.

Thanks in advance.

Kind Regards,

MrAkki

Answer 1

Try it like this:

DECLARE @xml XML='<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>';  

The query will define your namespace with the alias 'ns'. I use a predicate, to get hands on the right property and return every property with a type-safe value. The rest is up to you...

WITH XMLNAMESPACES('www.microsoft.com/SqlServer/SSIS' AS ns)
SELECT prm.value('(@ns:Name)[1]','nvarchar(max)') AS Parameter_Name
      ,prm.value('(ns:Properties/ns:Property[@ns:Name="ID"]/text())[1]','nvarchar(max)') AS Parameter_ID
      ,prm.value('(ns:Properties/ns:Property[@ns:Name="IncludeInDebugDump"]/text())[1]','bit') AS Parameter_IncludeInDebugDump
FROM @xml.nodes('/ns:Parameters/ns:Parameter') AS A(prm)