June 30, 2010 at 1:45 pm
I have not done any XML importing before and I know the data that I am starting with is not in an acceptable format. What can be done in TSQL to correct the incoming format before passing it to sp_xml_preparedocument?
<Employee>
<SSN>987654321</SSN>
<Employee>
<FirstName>MOREEN </FirstName>
<MiddleName>B</MiddleName>
<LastName>CORN </LastName>
<Suffix/>
</Employee>
<TotalWages>10244.28</TotalWages>
<TaxableWages>110.00</TaxableWages>
<OutOfStateWages>
<TotalWages> 0.00</TotalWages>
<TaxableWages> 0.00</TaxableWages>
<TaxState>XX</TaxState>
</OutOfStateWages>
</Employee>
<Employee>
<SSN>123456789</SSN>
<Employee>
<FirstName>ANDREW </FirstName>
<MiddleName>R</MiddleName>
<LastName>ROSS </LastName>
<Suffix/>
</Employee>
<TotalWages>20477.49</TotalWages>
<TaxableWages>1020.00</TaxableWages>
<OutOfStateWages>
<TotalWages> 0.00</TotalWages>
<TaxableWages> 0.00</TaxableWages>
<TaxState>XX</TaxState>
</OutOfStateWages>
</Employee>
Thanks for your suggestions.
June 30, 2010 at 2:49 pm
Just add a root
-------------------
DECLARE @yourText varchar(10000)
SET @yourText ='
<Employee>
<SSN>987654321</SSN>
<Employee>
<FirstName>MOREEN </FirstName>
<MiddleName>B</MiddleName>
<LastName>CORN </LastName>
<Suffix/>
</Employee>
<TotalWages>10244.28</TotalWages>
<TaxableWages>110.00</TaxableWages>
<OutOfStateWages>
<TotalWages> 0.00</TotalWages>
<TaxableWages> 0.00</TaxableWages>
<TaxState>XX</TaxState>
</OutOfStateWages>
</Employee>
<Employee>
<SSN>123456789</SSN>
<Employee>
<FirstName>ANDREW </FirstName>
<MiddleName>R</MiddleName>
<LastName>ROSS </LastName>
<Suffix/>
</Employee>
<TotalWages>20477.49</TotalWages>
<TaxableWages>1020.00</TaxableWages>
<OutOfStateWages>
<TotalWages> 0.00</TotalWages>
<TaxableWages> 0.00</TaxableWages>
<TaxState>XX</TaxState>
</OutOfStateWages>
</Employee>
'
set @yourText = '<root>'+@yourText+'</root>'
---- then you can use sp_xml_preparedocument-------
DECLARE @hdoc int
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @yourText
-- Remove the internal representation.
EXEC sp_xml_removedocument @hdoc
June 30, 2010 at 2:58 pm
Wonderful, thank you. This is the next which I apparently didn't right either. Would you tell me what I have wrong please.
create table xmlMWR (SSN varchar(9), TaxableWages varchar(20))
INSERT INTO xmlMWR
(
SSN ,
TaxableWages
)
SELECT SSN , TaxableWages
FROM OPENXML (@docHandle, '/employee')
WITH (
SSN VARCHAR(9) 'SSN',
TaxableWages VARCHAR(20) 'TaxableWages')
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply