Solved

XML Select

Posted on 2011-03-04
8
474 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
  • 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
 
LVL 41

Expert Comment

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

select @xml = '<?xml version="1.0" encoding="UTF-16" ?>
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now