March 27, 2017 at 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
March 27, 2017 at 9:14 am
MrAkki - Monday, March 27, 2017 9:14 AMHi 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 : ....
March 27, 2017 at 9:15 am
MrAkki - Monday, March 27, 2017 9:14 AMMrAkki - Monday, March 27, 2017 9:14 AMHi 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,
MrAkkiThe smilies should be : ....
I mean : and a P with out
March 27, 2017 at 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)
;
March 28, 2017 at 12:48 am
Thom A - Monday, March 27, 2017 9:18 AMWhat 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.)
March 28, 2017 at 12:56 am
Eirikur Eiriksson - Monday, March 27, 2017 9:27 AMThis 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?
March 28, 2017 at 1:05 am
MrAkki - Tuesday, March 28, 2017 12:56 AMEirikur Eiriksson - Monday, March 27, 2017 9:27 AMThis 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