SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Using nodes()
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Bex
Aged Yak Warrior

United Kingdom
580 Posts

Posted - 03/19/2013 :  12:16:48  Show Profile  Send Bex an AOL message  Reply with Quote
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

USA
15635 Posts

Posted - 03/19/2013 :  13:57:12  Show Profile  Visit robvolk's Homepage  Reply with Quote
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)
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.
Go to Top of Page

Bex
Aged Yak Warrior

United Kingdom
580 Posts

Posted - 03/20/2013 :  04:35:04  Show Profile  Send Bex an AOL message  Reply with Quote
Thanks Rob

I'll have a play with it today :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000