? on Parsing an XML field

• jbalbo

  SSCertifiable

  Points: 7580

  More actions

  September 18, 2017 at 12:45 pm

  #332716

  Hi

  Can I parse  an XML field as below,
  For example parse out "Review Type"

  Thanks
  Joe

  <Parameters xmlns="http://www.any.xsd">
  <Parameter Name="Addendum2" StringValue="True" />
  <Parameter Name="ReviewType" StringValue="140" />
  <Parameter Name="Addendum_First_UserID" StringValue="4144296331" />
  etc...

• Thom A

  SSC Guru

  Points: 99126

  More actions

  September 18, 2017 at 12:52 pm

  #1959861

  Yes, you can. There are a lot of examples on the internet on how do so. I, myself, have to Google everytime I work with XML, as the syntax never sticks in my head. 🙂

  Have a quick Google/Bing/etc of something like "Query XML T-SQL" and have a go. If you get stuck, post back what you've tried and we'll be able to give you some pointers and help on where you may have gone wrong.

  Thom~

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

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  September 18, 2017 at 10:25 pm

  #1959893

  Here is something to get you started
  😎
  

  USE TEEST;
GO
SET NOCOUNT ON;

  DECLARE @TXML XML = '<ROOT>
<Parameters xmlns="http://www.any.xsd">
<Parameter Name="Addendum2" StringValue="True" />
<Parameter Name="ReviewType" StringValue="140" />
<Parameter Name="Addendum_First_UserID" StringValue="4144296331" />
</Parameters>
</ROOT>';

  SELECT
  PARAM.DATA.value('@Name'   ,'VARCHAR(50)') AS PARAM_Name
 ,PARAM.DATA.value('@StringValue','VARCHAR(50)') AS PARAM_StringValue
FROM @TXML.nodes('*:ROOT/*:Parameters/*:Parameter') PARAM(DATA);


• jbalbo

  SSCertifiable

  Points: 7580

  More actions

  September 19, 2017 at 8:25 am

  #1959948

  Thanks

  A "bit" confused, but let me do some googling and I'm sure I'll be back with questions....

  Thanks Again

• jbalbo

  SSCertifiable

  Points: 7580

  More actions

  September 19, 2017 at 8:42 am

  #1959951

  So Maybe I'm close???

  SELECT
   [SqlParameters].value('declare namespace ns="http://www.any.com/WebFramework/WorkflowParameters.xsd"; (/ns:Parameters/ns:Addendum2)[1]','varchar(50)') AS TEST
  FROM [WorkflowInstances]

  but erring on no element found 
  Trying to parse Addendum2
  using

  <Parameters xmlns="http://www.any.xsd">
  <Parameter Name="Addendum2" StringValue="True" />
  <Parameter Name="ReviewType" StringValue="140" />
  <Parameter Name="Addendum_First_UserID" StringValue="4144296331" />
  etc...

• Thom A

  SSC Guru

  Points: 99126

  More actions

  September 19, 2017 at 9:03 am

  #1959955

  jbalbo - Tuesday, September 19, 2017 8:42 AM

  So Maybe I'm close???

  SELECT
   [SqlParameters].value('declare namespace ns="http://www.any.com/WebFramework/WorkflowParameters.xsd"; (/ns:Parameters/ns:Addendum2)[1]','varchar(50)') AS TEST
  FROM [WorkflowInstances]

  but erring on no element found 
  Trying to parse Addendum2
  using

  <Parameters xmlns="http://www.any.xsd">
  <Parameter Name="Addendum2" StringValue="True" />
  <Parameter Name="ReviewType" StringValue="140" />
  <Parameter Name="Addendum_First_UserID" StringValue="4144296331" />
  etc...

  Almost there, try this:
  USE Sandbox;
GO
CREATE TABLE Test (SampleXML xml);
GO

  INSERT INTO Test
VALUES ('<Parameters xmlns="http://www.any.xsd">
<Parameter Name="Addendum2" StringValue="True" />
<Parameter Name="ReviewType" StringValue="140" />
<Parameter Name="Addendum_First_UserID" StringValue="4144296331" />
</Parameters>');
GO
WITH XMLNAMESPACES (DEFAULT 'http://www.any.xsd')
SELECT P.d.value('@Name','varchar(50)') AS ParameterName
FROM Test T
  CROSS APPLY T.SampleXML.nodes('Parameters/Parameter') P(d);
GO

  GO

  DROP TABLE Test;
GO

  You need to declare your namescape using the WITH syntax, and then you can query the XML "as normal".
  

  Thom~

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

• jbalbo

  SSCertifiable

  Points: 7580

  More actions

  September 19, 2017 at 9:21 am

  #1959966

  Great Sample..

  Thanks you (even understand it..lol )
  here is what I came up with 

  Thanks Again
  Joe

  WITH XMLNAMESPACES (DEFAULT 'http://www.any.xsd')
  SELECT [Title],[view as]
  --P.d.value('@Name','varchar(50)') AS ParameterName
  --,P.d.value('@StringValue','varchar(50)') AS Parametervalue
  FROM [WorkflowInstances] T
   CROSS APPLY T.[SqlParameters].nodes('Parameters/Parameter') P(d)
   where P.d.value('@Name','varchar(50)') = 'ProgramSite' and P.d.value('@StringValue','varchar(50)') = '101'
   ;

• Alan Burstein

  SSC Guru

  Points: 61136

  More actions

  September 19, 2017 at 9:31 am

  #1959973

  Eirikur Eiriksson - Monday, September 18, 2017 10:25 PM

  Here is something to get you started
  😎
  

  USE TEEST;
GO
SET NOCOUNT ON;

  DECLARE @TXML XML = '<ROOT>
<Parameters xmlns="http://www.any.xsd">
<Parameter Name="Addendum2" StringValue="True" />
<Parameter Name="ReviewType" StringValue="140" />
<Parameter Name="Addendum_First_UserID" StringValue="4144296331" />
</Parameters>
</ROOT>';

  SELECT
  PARAM.DATA.value('@Name'   ,'VARCHAR(50)') AS PARAM_Name
 ,PARAM.DATA.value('@StringValue','VARCHAR(50)') AS PARAM_StringValue
FROM @TXML.nodes('*:ROOT/*:Parameters/*:Parameter') PARAM(DATA);


  Forum control messed up the code... changed the xml element names to start with xx since : + p gives you :p

  
DECLARE @TXML XML = '<ROOT>
<xxParameters xmlns="http://www.any.xsd">
<xxParameter Name="Addendum2" StringValue="True" />
<xxParameter Name="ReviewType" StringValue="140" />
<xxParameter Name="Addendum_First_UserID" StringValue="4144296331" />
</xxParameters>
</ROOT>';

  SELECT
PARAM.DATA.value('@Name' ,'VARCHAR(50)') AS PARAM_Name,
PARAM.DATA.value('@StringValue','VARCHAR(50)') AS PARAM_StringValue
FROM @TXML.nodes('ROOT/*:xxParameters/*:xxParameter') PARAM(DATA);

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  September 19, 2017 at 6:13 pm

  #1960064

  I've found that it's much more fun to visit the person sending you the XML and just beat the crap out of them. 😉

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)