Avatar of Larry Brister
Larry Brister
Flag for United States of America asked on

XML Select

My attached code works exactly the way I need...but

I REALLY need the line
'<xml version="1.0" encoding="UTF-8"/>

To be this
<?xml version="1.0" encoding="UTF-8"?>

But everytime I try that it errors out
Declare @packet varchar(30)
Set @packet =	(SELECT '0000' + Cast(CAST(RAND() * 1000000000 AS INT) as varchar) + 
				Cast(CAST(RAND() * 1000000000 AS INT) as varchar))

Declare @clientAcctNum	varchar(10)
Set @clientAcctNum = '00123456'

Declare @clientUserID	varchar(10)
Set @clientUserID = '002345'

Declare @contactEmail	varchar(30)
Set @contactEmail = 'lbristerg@mydomain.com'

Declare @contactName	varchar(50)
Set @contactName = 'Bill Gatesg'

Declare @contactPhone	varchar(12)
Set @contactPhone = '(111)222-3333'

declare @xml xml
select @xml = '<xml version="1.0" encoding="UTF-8"/>
<Document>
  <XMLVersion Version="2.00"/>
  <Header>
       <PacketNum>' + @packet + '</PacketNum>
       <Test Choice="No"/>
       <ClientAccountID>' + @clientAcctNum + '</ClientAccountID>
       <ClientUserID>' + @clientUserID + '</ClientUserID>
       <ContactEmail>' + @contactEmail + '</ContactEmail>
       <ContactName>' + @contactName + '</ContactName>
       <ContactPhone>' + @contactPhone + '</ContactPhone>
       <SourceID/>
  </Header>' 
+ (
select top 1
      id, 
      firstName 'Debtors/Names/IndividualName/FirstName',
      lastName 'Debtors/Names/IndividualName/LastName'
from
      r_users
for xml path('Record')
) + '
</Document>'

select @xml

Open in new window

Microsoft SQL Server 2005Microsoft SQL Server 2008Microsoft SQL Server

Avatar of undefined
Last Comment
Larry Brister

8/22/2022 - Mon
kaufmed

I'm sure I'm missing something here, but shouldn't line 21 be:
select @xml = '<?xml version="1.0" encoding="UTF-8" ?>

Open in new window

Larry Brister

ASKER
kaufmed
That's exactly what I thought...but I'm getting this error...

Msg 9402, Level 16, State 1, Line 21
XML parsing: line 1, character 39, unable to switch the encoding
ralmada

I might be wrong but the ? there sounds like a malformed XML.

Check this link:
http://social.msdn.microsoft.com/Forums/en/sqlxml/thread/c58b21b6-cd54-400a-9b40-730965d4b59f
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ralmada

so basically try with utf-16

select @xml = '<?xml version="1.0" encoding="UTF-16" ?>
Larry Brister

ASKER
ralmada:
That ran without an error...but the output had no '<?xml version="1.0" encoding="UTF-16"  tag at the top

It started with "Document"
ASKER CERTIFIED SOLUTION
ralmada

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Larry Brister

ASKER
ralmada:
 You're correct.
Just found out that the url I post to will have that...I just need the > Document information
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Larry Brister

ASKER
Thanks