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)
 Another xml question - value()

Author  Topic 

Bex
Aged Yak Warrior

580 Posts

Posted - 2013-03-21 : 08:56:46
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

580 Posts

Posted - 2013-03-21 : 09:48:43
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

580 Posts

Posted - 2013-03-21 : 10:19:46
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

580 Posts

Posted - 2013-03-21 : 10:24:31
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
   

- Advertisement -