Try this

SELECT rd.id,

x.r.value('local-name(.)','varchar(30)') AS Element,

...

Your XML isn't valid, but this is roughly what you want

update t1

set jobno = t2.jobxml.value('(/root/job/@jobno)[1]','int')

from tblJob t1

inner join #tmpJob t2 on t2.jobid = t1.jobid

Eirikur Eiriksson (7/17/2016)

---

Slight difference in the query but huge in performance by using the text() function

😎

;WITH XML_CONVERT AS

(

SELECT

XN.XN_ID

...

If you have a fixed number of Name/Value pairs you can do this

SELECT TOP 1000

[AppActionNameID]

,CAST([AppActionParams] AS XML)

,CAST([AppActionParams] AS XML).value('(/BounceProperties/Name[1])[1]','varchar(20)') AS Name1

,CAST([AppActionParams] AS XML).value('(/BounceProperties/Value[1])[1]','varchar(50)') AS Value1

,CAST([AppActionParams] AS XML).value('(/BounceProperties/Name[2])[1]','varchar(20)') AS Name2

,CAST([AppActionParams]...

You can concatenate using 'FOR XML PATH...'

DECLARE @mytable TABLE(PolNo INT, [YEAR] INT, Platform VARCHAR(3), Number INT, Record INT, memo VARCHAR(30))

INSERT INTO @mytable(PolNo, [YEAR], Platform, Number, Record, memo)

VALUES

(123,2010,'pc',1,1,'The user had issues'),

(123,2010,'pc',1,2,'with...

Try this

with src as (

select d.Area,d.AccountNum,d.PolicyNum,d.SetId,d.LocId,d.LocDedAmt,d.NonLocDedAmt,

(select cast(d2.SetId as varchar(10)) + ',' as "text()"

from @DedTest d2

where d2.Area = d.Area

and d2.AccountNum = d.AccountNum

and d2.PolicyNum...

Really enjoyable few days, lots of excellent presentations, Itzik Ben-Gans was simply brilliant. Also got a chance to briefly meet Grant and Steve.

Finally got a chance to go this year - doing Wednesday to Saturday.

Alan.B (2/23/2016)

---

I don't know if anyone is aware of this but I noticed Dwain Camps hadn't posted anything in awhile so I checked his blog (dwaincsql.com), simple-talk, twitter, linkedin... then...

A couple of tweaks to Chris's query should do it

SELECT

*

,dr=DENSE_RANK() OVER(PARTITION BY pkLocalPatientLocalID ORDER BY grp)

FROM

(

SELECT

*

,grp=MIN(StartDateTime) OVER(PARTITION BY pkLocalPatientLocalID,WardLocation,rn2 -...

Does your XML have an encoding attribute such as

<?xml version="1.0" encoding="UTF-16"?>

Try using Jeff Modens string splitter

http://www.sqlservercentral.com/articles/Tally+Table/72993/

SELECT s.Item AS Sponsor

FROM NewTreeTable n

CROSS APPLY dbo.DelimitedSplit8k(n.Tree,'\') s

WHERE n.AccountID = 1496

AND s.Item <> n.AccountID;

Nice question, thanks.

srikantchary4u (5/25/2015)

---

Thanks a million guys especially to Mark Cowne.

You made my day.

This is my first post to this forum and it worked like a charm..Thanks again 🙂 !!

Hoping to come...

Your requirements are a bit unclear but this may work for you

WITH CTE AS(

SELECT book,position,

ROW_NUMBER() OVER(PARTITION BY position ORDER BY book) AS rn

FROM...