? ON Parsing an XML Field

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  June 16, 2020 at 2:26 pm

  #3763466

  Hi,

  I know I've asked this before and got a good link , but can't figure out how to parse this XML field.

  I would usually parse something like Data.Value('Data/.../..)

  But not sure how to parse out say "Case" from the field below(SqlParameters)?

  <Parameters xmlns="http://www.www.com/WebFramework/WorkflowParameters.xsd">

  <Parameter Name="ClientID" StringValue="12903166" />

  <Parameter Name="Workflow_ActiveSlide" StringValue="317" />

  <Parameter Name="Document_First_UserID" StringValue="8577430" />

  <Parameter Name="Program" StringValue="120555" />

  <Parameter Name="Workflow_ActiveUser" StringValue="9137070" />

  <Parameter Name="Document" StringValue="14317336" />

  <Parameter Name="Case" StringValue="12903171" />

  <Parameter Name="ProgramSite" StringValue="152" />

  </Parameters>

  Thanks

   

• drew.allen

  SSC Guru

  Points: 77013

  More actions

  June 16, 2020 at 3:00 pm

  #3763483

  This should get you started.

  DECLARE @doc XML =
  '<Parameters xmlns="http://www.www.com/WebFramework/WorkflowParameters.xsd">
  <Parameter Name="ClientID" StringValue="12903166" />
  <Parameter Name="Workflow_ActiveSlide" StringValue="317" />
  <Parameter Name="Document_First_UserID" StringValue="8577430" />
  <Parameter Name="Program" StringValue="120555" />
  <Parameter Name="Workflow_ActiveUser" StringValue="9137070" />
  <Parameter Name="Document" StringValue="14317336" />
  <Parameter Name="Case" StringValue="12903171" />
  <Parameter Name="ProgramSite" StringValue="152" />
  </Parameters>'
  
  SELECT c.value('@StringValue', 'NVARCHAR(20)')
  FROM @doc.nodes('/*:Parameters/*:Parameter[@Name="Case"]') T(c)

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Phil Parkin

  SSC Guru

  Points: 247172

  More actions

  June 16, 2020 at 3:07 pm

  #3763489

  Another version:

  CREATE TABLE #X
  (
      SomeXML XML
  );
  
  INSERT #X
  (
      SomeXML
  )
  VALUES
  ('<Parameters xmlns="http://www.www.com/WebFramework/WorkflowParameters.xsd">
  <Parameter Name="ClientID" StringValue="12903166" />
  <Parameter Name="Workflow_ActiveSlide" StringValue="317" />
  <Parameter Name="Document_First_UserID" StringValue="8577430" />
  <Parameter Name="Program" StringValue="120555" />
  <Parameter Name="Workflow_ActiveUser" StringValue="9137070" />
  <Parameter Name="Document" StringValue="14317336" />
  <Parameter Name="Case" StringValue="12903171" />
  <Parameter Name="ProgramSite" StringValue="152" />
  </Parameters>');
  
  SELECT Name = n.n1.value('(@*:Name)[1]', 'varchar(100)')
        ,val = n.n1.value('(@*:StringValue)[1]', 'varchar(100)')
  FROM #X XTab
      CROSS APPLY XTab.SomeXML.nodes('/*:Parameters/*:Parameter') n(n1);

  ---

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  June 16, 2020 at 5:29 pm

  #3763549

  Thank you  for the info, I get it now!!! sort of...:) bit more understanding on my part, but I see how to do it

   

  Thank You Again