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)
 Another xml question - value()
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Bex
Aged Yak Warrior

United Kingdom
580 Posts

Posted - 03/21/2013 :  08:56:46  Show Profile  Send Bex an AOL message  Reply with Quote
I have the following xml:


declare
	@XML xml,
	@elementName nvarchar(max) = 'casevalue',
	@row int = 1,
	@attributeName nvarchar(max) = 'value'

set @XML = '<DynoFormData xmlns:x="http://sysrepublic.com/Secure/4.0/DSL/ICMS/DynoFormData">
  <externalcase>
    <reporteremployeeid value="0000024" />
    <reportpremareremployeenamest value="SAM FRANK, Sales Associate" />
    <locationtype value="Store" />
    <locationcode surface-as="LocationID" value="00844" />
    <locationname value="TORONTO CONFERENCE C" />
    <typeofincident value="75555555" />
    <typeofcreditcard value="Discover" />
    <locationofincident value="22" />
    <dateofincident surface-as="IncidentDate" value="2013/03/07" />
    <timeofincident surface-as="IncidentTime" value="22:13:00" />
    <casevalue value="22.17" symbol="US$" currencyCode="USD" baseValue="0.00" baseCurrencyCode="USD" />
    <casevalue value="3.50" symbol="US$" currencyCode="USD" baseValue="0.00" baseCurrencyCode="USD" />
    <casevalue value="148956.01" symbol="US$" currencyCode="USD" baseValue="0.00" baseCurrencyCode="USD" />
   <incidentdetails value="test" />
    <capturedonvideo value="NA" />
    <assistedby value="0" />  
    </externalcase>
</DynoFormData>'

SELECT @XML.value('declare namespace DFD="http://sysrepublic.com/Secure/4.0/DSL/ICMS/DynoFormData";
							(/DFD:DynoFormData/DFD:*/DFD:*[local-name()=sql:variable("@elementName") and @row=sql:variable("@row")]/@*[local-name()=sql:variable("@attributeName")])[1]', 'nvarchar(200)')


Returns NULL??


I want to return:
1) a single value specifying [1], [2] or [3]
2) all the values for the element and the attribute:
22.17
3.50
148956.01

Thanks

Bex
Aged Yak Warrior

United Kingdom
580 Posts

Posted - 03/21/2013 :  09:48:43  Show Profile  Send Bex an AOL message  Reply with Quote
Got it working. Had to remove namespace in the xml blob:

;with xmlnamespaces ('http://sysrepublic.com/Secure/4.0/DSL/ICMS/DynoFormData' as DFD)
SELECT @XML.value('(/DFD:DynoFormData/DFD:externalcase/DFD:casevalue/@value)[2]', 'nvarchar(200)')

;with xmlnamespaces ('http://sysrepublic.com/Secure/4.0/DSL/ICMS/DynoFormData' as DFD)
SELECT a.b.value('@value', 'nvarchar(200)')
FROM @XML.nodes('/DFD:DynoFormData/DFD:externalcase/DFD:casevalue') as a(b)


However, I now want to get it working using the parameters??? No joy as of yet. If someone can look at this and go 'AHA!' that would be great. Otherwise this is going to take me another few hours.......

I used to be able to do this once upon a time............
Go to Top of Page

Bex
Aged Yak Warrior

United Kingdom
580 Posts

Posted - 03/21/2013 :  10:19:46  Show Profile  Send Bex an AOL message  Reply with Quote
Update.....

Can get it working with parameters for the elements:


;with xmlnamespaces ('http://sysrepublic.com/Secure/4.0/DSL/ICMS/DynoFormData' as DFD)
SELECT t.col.value('@value', 'nvarchar(200)')
FROM @XML.nodes('/DFD:DynoFormData/DFD:*[local-name()=sql:variable("@path")]/DFD:*[local-name()=sql:variable("@elementName")]')
AS t(col)


But not with the attribute defined as an element:

;with xmlnamespaces ('http://sysrepublic.com/Secure/4.0/DSL/ICMS/DynoFormData' as DFD)
SELECT t.col.value('@*[local-name()=sql:variable("@attributeName")]', 'nvarchar(200)')
FROM @XML.nodes('/DFD:DynoFormData/DFD:*[local-name()=sql:variable("@path")]/DFD:*[local-name()=sql:variable("@elementName")]')
AS t(col)


Error message:

Msg 2389, Level 16, State 1, Line 52
XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'
Go to Top of Page

Bex
Aged Yak Warrior

United Kingdom
580 Posts

Posted - 03/21/2013 :  10:24:31  Show Profile  Send Bex an AOL message  Reply with Quote
Got there eventually:

;with xmlnamespaces ('http://sysrepublic.com/Secure/4.0/DSL/ICMS/DynoFormData' as DFD)
SELECT t.col.value('.', 'nvarchar(200)')
FROM @XML.nodes('/DFD:DynoFormData/DFD:*[local-name()=sql:variable("@path")]/DFD:*[local-name()=sql:variable("@elementName")]/@*[local-name()=sql:variable("@attributeName")]')
AS t(col)
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.08 seconds. Powered By: Snitz Forums 2000