June 17, 2003 at 12:13 am
Dagsê (hi there)
I am looking for a way to format a clients table into the following XML
<client client_id="1">
<cl_fname>koos</cl_fname>
<cl_lname>grobler</cl_lname>
...
</client>
My table columns are obviously cl_fname etc.
I did get some help from a previous posting where a new lets-talk-about-south-african-rugby-and-stuff newsgroup was apparently started by asthor and crappy.
So far I have used Select client_id, cl_fname, cl_lname from clients as client for XML Auto, Elements, but it does not give me the id tag as attribute to the root tag.
I am sure that FOR XML EXPLICIT might do the trick, but I am not sure how.
Any help?
Groetnis (cheers)
Ruan
June 17, 2003 at 12:26 am
lol. The rugby lives on...
You can use AUTO, ELEMENTS.
What you do need to add is an alias for your table.
If you want to change the element and or attribute names, alias you columns.
eg:
Select FirstName From Clients ROOT
FOR XML AUTO, ELEMENTS
This will give you a document starting with ROOT and each element will be called FirstName.
If you add an alias to the FirstName column, say MyFirstName, the elements will be called MyFirstName.
eg:
Select
FirstName as MyFirstName
From Client ROOT
FOR XML AUTO, ELEMENTS
ROOT could also be anything you want.
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 17, 2003 at 1:26 am
Hi Crispin
I used the following:
Select client_id, cl_fname, cl_lname FROM clients AS Client for XML AUTO, ELEMENTS
This gives me:
<Client>
<client_id>1</client_id>
<cl_fname>koos</cl_fname>
<cl_lname>Viljoen</cl_lname>
</Client>
This is ok, but i am actually looking for:
<Client client_id="1">
<cl_fname>koos</cl_fname>
<cl_lname>Viljoen</cl_lname>
</Client>
How would I have to change the query if at all it is possible
Cheers
ruan
June 17, 2003 at 1:32 am
The problem you have there is lack of a root node. Not to serious though.
To get exactly what you want, have a look at EXPLICIT. You can do it with that.
There is an example in BOL which does that useing Pubs DB. That is what I used and hacked it apart.
Another solution for you could be using attributes. Not sure if the required document is cast in stone yet.
Just remove the , Elements and you'll have
<ROOT>
<Client ClientID="1" FirstName="Crispin"/>
<Client ClientID="2" FirstName="Crispin2"/>
</ROOT>
Is this what you are after.
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 17, 2003 at 1:53 am
That could work, I will then have to use a lot of XPath expression which is not neccesarily a bad thing.
If I could however perhaps get something like this:
<ROOT>
<Client client_id="1">
<cl_fname>koos</cl_fname>
<cl_lname>viljoen</cl_lname>
</client>
</ROOT>
would it be possible with AUTO, or will I have to EXPLICIT
This form of XML is mostly what I am going for, but the Explicit from BOL is pretty confusing, but if it the only way, I'll do it
Ruan
June 17, 2003 at 2:00 am
Nope, you gonna have to use EXPLICIT.
It was confusing for me as well. Once the lights get turned on, it became quite easy.
It's extremely powerfull!
Have a look at the exqamples in there and try change them to suite. If you get stuck, shout 🙂
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 18, 2003 at 6:28 am
Hi Eamon
thanx for the help, but I sort of figured out a way to do it with EXPLICIT.
Thanx anyway
Ruan
June 18, 2003 at 12:19 pm
ruan,
could you please post how and what you did with explicit
thank you
June 19, 2003 at 3:54 pm
Look at the topic "Using EXPLICIT Mode" in BOL. Even though the syntax is very strange it works very well. Basically you have to know precisely what your XML needs to look like. Then set up each node with a separate statement all joined together with unions on the Tag,Parent combination.
IE From BOL:
SELECT 1 as Tag,
NULL as Parent,
Customers.CustomerID as [Customer!1!CustomerID],
NULL as [Order!2!OrderID]
FROM Customers
UNION ALL
SELECT 2,
1,
Customers.CustomerID,
Orders.OrderID
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
ORDER BY [Customer!1!CustomerID], [Order!2!OrderID]
FOR XML EXPLICIT
I hope this helps!
Gary Johnson
Gary Johnson
DBA
Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
June 20, 2003 at 1:53 am
Hi gljjr
I understand the basic syntax of how the EXPLICIT mode works, I just want to know if I need to go change my tables to look like a Universal table, or do I need to create a new Universal table
Thanx
Ruan
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply