? ON Parsing an XML Field

  • 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

     

  • 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

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

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • 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

     

Viewing 4 posts - 1 through 3 (of 3 total)

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