Using nodes() - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Using nodes()

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2013-03-19 : 12:16:48

I have xml column with following sample:

<DynoFormData xmlns="http://sysrepublic.com/Secure/4.0/DSL/ICMS/DynoFormData">
  <incidentreportemployeeinjury>
    <confirmationmessage value="Confirmation Message" />
    <incidentissaved surface-as="IncidentIsSaved" value="No" />
    <reporteremployeeid value="0000024" />
    <reportertitle value="Sales Associate" />
    <reporterfirstname value="SAM" />
    <reporterlastname value="FRANK" />
    <reporterphonenumber value="9790728289" />
    <locationcode surface-as="LocationID" value="2015" />
...............
  </incidentreportemployeeinjury>
</DynoFormData>

I want to get the values from the reportertitle element across all columns in the table (amongst other things). How do I do that using nodes() and CROSS APPLY?

I've looked at msdn, it is at the end of the day, I'm tired, lacking concentration, and as a result, what they have put looks like complete nonsense. I'm getting no where

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-03-19 : 13:57:12

[code]DECLARE @x XML='<DynoFormData xmlns:x="http://sysrepublic.com/Secure/4.0/DSL/ICMS/DynoFormData">
<incidentreportemployeeinjury>
<confirmationmessage value="Confirmation Message" />
<incidentissaved surface-as="IncidentIsSaved" value="No" />
<reporteremployeeid value="0000024" />
<reportertitle value="Sales Associate" />
<reporterfirstname value="SAM" />
<reporterlastname value="FRANK" />
<reporterphonenumber value="9790728289" />
<locationcode surface-as="LocationID" value="2015" />
</incidentreportemployeeinjury>
<incidentreportemployeeinjury>
<confirmationmessage value="Confirmation Message" />
<incidentissaved surface-as="IncidentIsSaved" value="No" />
<reporteremployeeid value="0000024" />
<reportertitle value="Duplicate" />
<reporterfirstname value="SAM" />
<reporterlastname value="FRANK" />
<reporterphonenumber value="9790728289" />
<locationcode surface-as="LocationID" value="2015" />
</incidentreportemployeeinjury>
</DynoFormData>'
SELECT x.value('@value[1]','varchar(50)')
FROM @x.nodes('//reportertitle') x(x)[/code]Notice I had to modify the namespace declaration to xmlns:x= in order for this to work correctly. I could not figure out how to get it working without a namespace alias.

Bex
Aged Yak Warrior

580 Posts

Posted - 2013-03-20 : 04:35:04

Thanks Rob

I'll have a play with it today :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources