create xml fragments

Question

create xml fragments

ashishchap

SSC Enthusiast

Points: 127
More actions
February 11, 2008 at 9:33 pm

#71311

Hi,
Im a beginner in sql server. I have a system where I need to create xml fragments out of a table, i.e. for each row of a table,
filter out the columns,
create an xml fragment in a definite format and
add the fragment to the xml in another table.
To give you a clear picture of what I am talking about, here is the example:
Here is the table (Name: Client)
ID Name Address CustomerType
-------------------------------------------------------------------
1 Rambo 1/1 Dante rd 3
2 Austin 2/2 Dante rd 5
3 Jerry 3/3 Dante rd 6
There is another table(Name:XmlTable)
ClientId ClientXml
-----------------------------
Now, I have to iterate through each row of the table , create an xml fragment, add the fragment to the corresponding client xml.
The fragment for the first row might look like:
{clientdetails}
{clientaddress}1/1 Dante rd{/clientAddress}
{clientname}Rambo{/clientName}
{/clientdetails}
The old client xml might be:
{Client}
{DOB}1/1/1900{/DOB}
{/Client}
After adding the fragment, it should look like
{Client}
{DOB}1/1/1900{/DOB}
{clientdetails}
{clientaddress}1/1 Dante rd{/clientAddress}
{clientname}Rambo{/clientName}
{/clientdetails}
{/Client}
Now this has to be done for each row in the table.
I tried to find a good example in the net, but was not successful. If anyone could direct me to a good example of iterating through the rows of a table, that would be great.
Cheers,
Ashish

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

kozloski SSC Eights! Points: 804 More actions · Answer 1

Ashish,

I don't have a test system in front of me right now, but hopefully can provide some pointers in the right direction. It appears that the "FOR XML" clause in T-SQL might be helpful for generating the XML fragments you need. It has a few different modes that give you varying levels of control over the XML output.

You might try this to start with:

SELECT * FROM Client FOR XML AUTO

and see what that gets you...

The SQL Server Books Online entry for "Constructing XML Using FOR XML" looks like a good entry point into the SQL Server documentation, as well. Here is the URL: http://msdn2.microsoft.com/en-us/library/ms178107.aspx

-- J.Kozloski, MCDBA, MCITP

ashishchap SSC Enthusiast Points: 127 More actions · Answer 2

Hi kozloski,

Thanks heaps for the reply. I've already done the tasks I was required to do, but my main concern for now is the performance. Here is the detailed steps of how I'm doing it now:

1. I have a stored procedure named bulkInsert which accepts the file path as string, rowterminator and fieldterminator. This will create a table (as staging) named Address1 and bulk insert the data onto it. It then calls another stored procedure, insertAddress which will filter the Address out of the table.

2. The insertAddress procedure will select the address and client id and store onto the main Address table. Now the processing for xml starts.

3. The code for iterating through each row and calling the clientxml procedure looks like:

DECLARE @xml XML
DECLARE @xmlQuery NVARCHAR(MAX)
DECLARE @ParameterList NVARCHAR(MAX)
DECLARE @clientID BIGINT
SET @xmlQuery =N'SELECT @client=ClientID FROM '+ @tableName
SET @ParameterList = N'@client BigInt OUTPUT'
Set rowcount 1
EXEC sp_executesql @xmlQuery, @ParameterList, @client=@clientID OUTPUT
SET @xmlQuery =N'SET @xmlVal1 = (SELECT ClientID AS ''ClientIdentifier'',
AddressLine1,AddressValid,CreatedDateTime
FROM '+ @tableName + '
WHERE ClientID='+cast(@clientID As NVARCHAR(MAX))+' FOR XML RAW(''ClientAddress''), TYPE, ELEMENTS)'
SET @ParameterList = N'@xmlVal1 XML OUTPUT'
EXEC sp_executesql @xmlQuery, @ParameterList, @xmlVal1=@xml OUTPUT
While @@rowcount <>0
BEGIN
EXEC ClientXml @ClientID,@xml,'ClientAddress'
Set rowcount 1
SET @xmlQuery= 'DELETE '+@tableName
EXEC (@xmlQuery)
SET @xmlQuery =N'SELECT @client=ClientID FROM '+ @tableName
SET @ParameterList = N'@client BigInt OUTPUT'
Set rowcount 1
EXEC sp_executesql @xmlQuery, @ParameterList, @client=@clientID OUTPUT
SET @xmlQuery =N'SET @xmlVal1 = (SELECT ClientID AS ''ClientIdentifier'',
AddressLine1,AddressValid,CreatedDateTime
FROM '+ @tableName + '
WHERE ClientID='+cast(@clientID As NVARCHAR(MAX)+'
FOR XML RAW(''ClientAddress''), TYPE, ELEMENTS)'
SET @ParameterList = N'@xmlVal1 XML OUTPUT'
EXEC sp_executesql @xmlQuery, @ParameterList, @xmlVal1=@xml OUTPUT
END

4. The Client Xml looks like:

DECLARE @clientXml as xml
DECLARE @clientStart as DATETIME
DECLARE @addFragment AS NVARCHAR(MAX)
SET @clientStart=getDate()
--check if the client xml exists in the database
If not exists (Select * from ClientXmlTable where clientId=@ClientID )
BEGIN
Set @clientXml= N'{?xml version = "1.0" encoding= "UTF-16"?}
{Client}
{ClientAddresses/}
{ClientNames /}
{/Client}'
Insert Into ClientXmlTable(ClientID,ClientXml) Values (@ClientID,@clientXml)
END
--adds the fragment to the clientxml
Set @addFragment= 'Update ClientXmlTable Set ClientXml.modify(''insert '
+ Cast(@xmlfragment as nvarchar(Max))
+ ' as last into (//'+ @node +')[1]'')
WHERE ClientID = '+ CAST(@clientID AS NVARCHAR(MAX))
exec (@addFragment)
END

The clientxml takes in clientid, a fragment of xml and the node name it should be inserted into. It then checks if the clientxml exists in the database and if not, creates a new one and saves it. It then inserts the data onto the node specified.

Now it does the job for me, but im really concerned about the speed. I tested it with a file consisting 10,000 rows (1.01MB) and it takes around 11 minutes to insert the 10,000 records. As you might have already noticed, I'm pretty new to T-SQL and SQL SERVER. If you could point me the flaws in my code and the ways I could improve it, I would really appreciate your help.

Cheers,

Ashish