Solved

XML Select

Posted on 2011-03-04
8
478 Views
Last Modified: 2012-05-11
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
Comment
Question by:lrbrister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 35037384
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
 

Author Comment

by:lrbrister
ID: 35037452
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
 
LVL 41

Expert Comment

by:ralmada
ID: 35037519
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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 41

Expert Comment

by:ralmada
ID: 35037535
so basically try with utf-16

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

Author Comment

by:lrbrister
ID: 35037587
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
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 35037742
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
 

Author Comment

by:lrbrister
ID: 35037775
ralmada:
 You're correct.
Just found out that the url I post to will have that...I just need the > Document information
0
 

Author Closing Comment

by:lrbrister
ID: 35037777
Thanks
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question