SQL XML - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL XML

Author

Topic

theboyholty
Posting Yak Master

226 Posts

Posted - 2012-03-29 : 05:43:48

I have to set up an XML . . . erm 'thing' for sending data to an associate system in a different company and would appreciate some guidance. I've been using XML auto and its close to what they want but not quite there.

Below is the example code. My problem is the address section. There's an extra element '<Address>' whioch contains all the address fields.
In my dataset I have AddressLine1, AddressLine2 etc. I therefore need to know how to add this additional element '<Address>' that contains my other address elements.

EXAMPLE XML (Provided by the client)

<ClientDetails>
	<Title>Mr</Title>
	<FirstName>Joe</FirstName>
	<Surname>Test</Surname>
	<Address>
		<AddressLine>1st Line of Address</AddressLine>
		<AddressLine>2nd Line of Address</AddressLine>
		<AddressLine>Town/ City</AddressLine>
		<AddressLine>County</AddressLine>
		<PostCode>PO5 7CD</PostCode>
	</Address>
</ClientDetails>

and this is the code I'm using so far:

WITH XMLNAMESPACES('UKPTest' as co)
SELECT 
	 Title
	,FirstName
	,Surname
	,DateOfBirth
	,AddressLine1
	,AddressLine2
	,City
	,County
	,Postcode
	,Telephone
	,EmailAddress
FROM UKPDataManagement.dbo.GMWebSelection
FOR XML AUTO, ELEMENTS, ROOT ('mainrootname')

Thanks

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-29 : 07:01:35

Would be much more predictable and easier to construct the XML using XML PATH than XML AUTO. However, if you do want to stick with XML AUTO, this is an example of how you can do it. I am aliasing the tables to match the element names (because that is what XML AUTO requires you to do).

CREATE TABLE #tmp 
(
	Title VARCHAR(32), Surname VARCHAR(32), 
	Address1 VARCHAR(32),Address2 VARCHAR(32)
);

INSERT INTO #tmp VALUES 
	('Mr','Smith','1 Washington Street', 'Stamford'),
	('Ms','Jones','2 Lincoln Ave', 'New York');

WITH XMLNAMESPACES('UKPTest' as co)
SELECT
	Title,
	Surname,
	Address.Address1,
	Address.Address2
FROM
	#tmp ClientDetails
	CROSS APPLY (
		select t2.Address1, t2.Address2
		from #tmp t2 where ClientDetails.Surname = t2.Surname
	) AS [Address] 
FOR XML AUTO, ELEMENTS, ROOT ('mainrootname')

DROP TABLE #tmp;

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-29 : 07:15:58

I meant to post the XML PATH query (in an attempt to convert you to the XML PATH method rather than XML auto), but messed up the posting. This is how you can generate the same XML using XML PATH

;WITH XMLNAMESPACES('UKPTest' as co)
SELECT
	Title,
	Surname,
	Address1 AS [Address/Address1],
	Address2 AS [Address/Address2]
FROM 
	#tmp
FOR XML PATH('ClientDetails'),ROOT ('mainrootname')

theboyholty
Posting Yak Master

226 Posts

Posted - 2012-03-29 : 08:04:03

Simple when you know how!

Thanks mate, that's great.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources