• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

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

0
lrbrister
Asked:
lrbrister
  • 4
  • 3
1 Solution
 
käµfm³d 👽Commented:
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

0
 
lrbristerAuthor Commented:
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
0
 
ralmadaCommented:
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
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
ralmadaCommented:
so basically try with utf-16

select @xml = '<?xml version="1.0" encoding="UTF-16" ?>
0
 
lrbristerAuthor Commented:
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"
0
 
ralmadaCommented:
Not 100% sure, but I understand that if the encoding information is not present, it will use the default, which could be UTF 16 already.

0
 
lrbristerAuthor Commented:
ralmada:
 You're correct.
Just found out that the url I post to will have that...I just need the > Document information
0
 
lrbristerAuthor Commented:
Thanks
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now