? ON Parsing an XML Field

Question

? 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

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

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

drew.allen SSC Guru Points: 76985 More actions · Answer 1

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: 246960 More actions · Answer 2

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

jbalbo SSCertifiable Points: 7583 More actions · Answer 3

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