Generating multiple rows with xml data column

[Ron]

I am trying to create a query that will generate multiple rows of output,
with each row containing a contract number and an xml document representing
the details of that contract. However, I have been unable to generate
anything other than one row of data with all contract information in that xml
type.

Is there a function that will allow me to create the xml column as I would
using the for xml path statement, with a column representing the contract
number?

TIA

[Peter W. DeBetta]

Ron,

Can you give the newsgroup readers more details, including your version of
SQL Server, your existing queries (working or not), DDL, and the like...
--
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--

Post by Ron
I am trying to create a query that will generate multiple rows of output,
with each row containing a contract number and an xml document
representing
the details of that contract. However, I have been unable to generate
anything other than one row of data with all contract information in that xml
type.
Is there a function that will allow me to create the xml column as I would
using the for xml path statement, with a column representing the contract
number?
TIA
--
Ron Kincaid

[Ron]

Yes, details would help. Blame the AC blowing out last night...
I'm using Sql 2005, migrating data from Sql 2000. Currently their data is
in relational tables. I would like to write a script where the new tables
are created with three columns: an Id column, a column representing a
contract number and a column representing an xml document of contract details.

If I write a query using the For XML Path statement, I can generate the xml
column details that I need, structured the way that I want EXCEPT that all of
the contract details are merged into one Xml document. I am trying to
generate one document per Contract Number, and, insert the results of the
query into the new tables. I can do all of this if I write the routines in
C# code, but it seems like I should be able to do it in T-Sql, and I am
looking for every opportunity to get deeper into T-Sql coding.

Thanks once again, I will check out Omnibuzz' answer, but felt I should
provide the details in the event that we come up w/ a solution that will
benefit more than me! :)
--
Ron Kincaid

Post by Peter W. DeBetta
Ron,
Can you give the newsgroup readers more details, including your version of
SQL Server, your existing queries (working or not), DDL, and the like...
--
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--

Post by Ron
I am trying to create a query that will generate multiple rows of output,
with each row containing a contract number and an xml document representing
the details of that contract. However, I have been unable to generate
anything other than one row of data with all contract information in that xml
type.
Is there a function that will allow me to create the xml column as I would
using the for xml path statement, with a column representing the contract
number?
TIA
--
Ron Kincaid

[unknown]

Here is an example of using FOR XML to compose old relational data into xml then shred into a new table. #contract is the header table from the old database and #details are the contract details. #newContract is the new table for contracts.

CREATE
--DROP
TABLE #contract
(
ID INT,
other INT
)

CREATE TABLE #details
(
ID INT,
Name VARCHAR(100),
Length INT
)


INSERT INTO #contract VALUES (1, 2)
INSERT INTO #contract VALUES (2, 2)
INSERT INTO #contract VALUES (3, 2)
INSERT INTO #contract VALUES (10, 2)



INSERT INTO #details VALUES (1, 'asdf', 3)
INSERT INTO #details VALUES (1, 'asfadf', 3)
INSERT INTO #details VALUES (1, 'asfdadf', 3)
INSERT INTO #details VALUES (1, 'cccasdf', 3)

INSERT INTO #details VALUES (2, 'asdf', 3)
INSERT INTO #details VALUES (2, 'asfadf', 3)
INSERT INTO #details VALUES (2, 'asfdadf', 3)
INSERT INTO #details VALUES (2, 'cccasdf', 3)

INSERT INTO #details VALUES (3, 'asdf', 3)
INSERT INTO #details VALUES (3, 'asfadf', 3)
INSERT INTO #details VALUES (3, 'asfdadf', 3)
INSERT INTO #details VALUES (3, 'cccasdf', 3)


INSERT INTO #details VALUES (10, 'asdf', 3)
INSERT INTO #details VALUES (10, 'asfadf', 3)
INSERT INTO #details VALUES (10, 'asfdadf', 3)
INSERT INTO #details VALUES (10, 'cccasdf', 3)

CREATE TABLE #newContract
(
ID INT IDENTITY PRIMARY KEY,
ContractID INT,
Contract xml
)


WITH contractXML(data)
AS
(
SELECT C.ID, D.Name, D.Length FROM #contract as c
JOIN #details AS D ON D.ID = C.ID
FOR XML AUTO, TYPE
)
INSERT INTO #newContract
SELECT T.C.value('@ID', 'INT'), T.C.query('.') FROM contractXML
CROSS APPLY data.nodes('c') AS T(C)


SELECT * FROM #newContract


ID ContractID Contract
----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1 <c ID="1"><D Name="asdf" Length="3" /><D Name="asfadf" Length="3" /><D Name="asfdadf" Length="3" /><D Name="cccasdf" Length="3" /></c>
2 2 <c ID="2"><D Name="asdf" Length="3" /><D Name="asfadf" Length="3" /><D Name="asfdadf" Length="3" /><D Name="cccasdf" Length="3" /></c>
3 3 <c ID="3"><D Name="asdf" Length="3" /><D Name="asfadf" Length="3" /><D Name="asfdadf" Length="3" /><D Name="cccasdf" Length="3" /></c>
4 10 <c ID="10"><D Name="asdf" Length="3" /><D Name="asfadf" Length="3" /><D Name="asfdadf" Length="3" /><D Name="cccasdf" Length="3" /></c>

[Ron]

I was able to use Dan's examples and get exactly what I wanted:

Declare @schwa xml

Set @Schwa = (Select ContractNum as contractNumber, etc as etc, etc
From owner.table for xml path(mePath), root(meRoot))
Insert Into owner.MyNewTable Select c.value('contractNumber[1]', 'int') as
ContractNumber, T.c.query('.') From
@schwa.nodes('/Document/ContractDocument') as T(c)

The result is an identity column, a contractNumber column that allows quick
searches for details, which are an xml doc. Thanks everyone, I think I'm
going to like Sql Server 2005!
--
Ron Kincaid

Post by unknown
Here is an example of using FOR XML to compose old relational data into xml then shred into a new table. #contract is the header table from the old database and #details are the contract details. #newContract is the new table for contracts.
CREATE
--DROP
TABLE #contract
(
ID INT,
other INT
)
CREATE TABLE #details
(
ID INT,
Name VARCHAR(100),
Length INT
)
INSERT INTO #contract VALUES (1, 2)
INSERT INTO #contract VALUES (2, 2)
INSERT INTO #contract VALUES (3, 2)
INSERT INTO #contract VALUES (10, 2)
INSERT INTO #details VALUES (1, 'asdf', 3)
INSERT INTO #details VALUES (1, 'asfadf', 3)
INSERT INTO #details VALUES (1, 'asfdadf', 3)
INSERT INTO #details VALUES (1, 'cccasdf', 3)
INSERT INTO #details VALUES (2, 'asdf', 3)
INSERT INTO #details VALUES (2, 'asfadf', 3)
INSERT INTO #details VALUES (2, 'asfdadf', 3)
INSERT INTO #details VALUES (2, 'cccasdf', 3)
INSERT INTO #details VALUES (3, 'asdf', 3)
INSERT INTO #details VALUES (3, 'asfadf', 3)
INSERT INTO #details VALUES (3, 'asfdadf', 3)
INSERT INTO #details VALUES (3, 'cccasdf', 3)
INSERT INTO #details VALUES (10, 'asdf', 3)
INSERT INTO #details VALUES (10, 'asfadf', 3)
INSERT INTO #details VALUES (10, 'asfdadf', 3)
INSERT INTO #details VALUES (10, 'cccasdf', 3)
CREATE TABLE #newContract
(
ID INT IDENTITY PRIMARY KEY,
ContractID INT,
Contract xml
)
WITH contractXML(data)
AS
(
SELECT C.ID, D.Name, D.Length FROM #contract as c
JOIN #details AS D ON D.ID = C.ID
FOR XML AUTO, TYPE
)
INSERT INTO #newContract
CROSS APPLY data.nodes('c') AS T(C)
SELECT * FROM #newContract
ID ContractID Contract
----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1 <c ID="1"><D Name="asdf" Length="3" /><D Name="asfadf" Length="3" /><D Name="asfdadf" Length="3" /><D Name="cccasdf" Length="3" /></c>
2 2 <c ID="2"><D Name="asdf" Length="3" /><D Name="asfadf" Length="3" /><D Name="asfdadf" Length="3" /><D Name="cccasdf" Length="3" /></c>
3 3 <c ID="3"><D Name="asdf" Length="3" /><D Name="asfadf" Length="3" /><D Name="asfdadf" Length="3" /><D Name="cccasdf" Length="3" /></c>
4 10 <c ID="10"><D Name="asdf" Length="3" /><D Name="asfadf" Length="3" /><D Name="asfdadf" Length="3" /><D Name="cccasdf" Length="3" /></c>

[Omnibuzz]

Hi Ron,
Can you give a sample data for the input table and the sample of your
result.
I have a feeling that you are creating an XML for no use. The result of the
following two statement can be obtained in one query, if I am not wrong,
which I can try to give if I see the data... (if you are interested, of
course :)

Post by Ron
From owner.table for xml path(mePath), root(meRoot))
Select c.value('contractNumber[1]', 'int') as
ContractNumber, T.c.query('.') From
@schwa.nodes('/Document/ContractDocument') as T(c)

[Ron]

Sure - let me give you a bit of background too. I'm working with an agency
who has multiple vendors and clients. The application for which I'm doing
this work is used by a partner of the agency. Their internal IT person and
the agency's internal IT people have asked for their data to be stored as xml
in the tables. Part of the effort involves migrating the old "relational"
data (and that's using relational very, very loosely) into the new tables
with schema that they have provided. So, that's why I was looking for a way
to get the data from several tables into a structured xml document and then
into another table.

I want the contract number column available because I will otherwise be
searching some reasonably large xml columns to find the right contract.

I know this is a bit lengthy, but I want you to have a bit of a feel for
what I'm trying to accomlish. As far as you having a different suggestion,
please have at it! This is my first foray into Sql Server 2005, and while I
understand relational database design, it is not what I do every day, so I'm
starting perhaps with a personal handicap.

Here's a sample of what I'm trying to accomplish:
contracts - a table with 3 columns, ContractId, ContractNumber,
ContractDocument
The id and number columns are integers. The document column is an xml
document. The point of my question here was a way to migrate the legacy data
to the new table. The sample routine I provided previously mirrors what I'm
planning to do. Here is a snippet of the document:

<pre>
<ContractDocument>
<contractNumber>2</contractNumber>
<employerName>Turnbull AC</employerName>
<employerId>7</employerId>
<bargainingUnit>T</bargainingUnit>
<localUnion>EIEIO</localUnion>
<nationalUnion>Old McDonald</nationalUnion>
<startDate>2006-01-01</startDate>
<endDate>2006-12-31</endDate>
<unitSize>62</unitSize>
<executionDate>2005-11-1</executionDate>
<contractStatus>ACT</contractStatus>
<numPages>342</numPages>
<microficheStatus>M</microficheStatus>
<caseNumber>84-CON-01-2061</caseNumber>
<negotiationStatus />
<initialContract>no</initialContract>
<reOpenDate1/>
<reOpenDate2/>
<reOpenDate3/>
<contractRead>yes</contractRead>
<lastUpdateDate>2001-07-19</lastUpdateDate>
<benchmarks>yes</benchmarks>
<effectiveReopenDate/>
</ContractDocument>
</pre>

The Xml column request comes from a partner of the agency for which I'm
doing the work. If you have suggestions for improvements, I'm glad to hear
them, keeping in mind that I might not be able to alter some things due to
the other parties involved.
--
Ron Kincaid

Post by Omnibuzz
Hi Ron,
Can you give a sample data for the input table and the sample of your
result.
I have a feeling that you are creating an XML for no use. The result of the
following two statement can be obtained in one query, if I am not wrong,
which I can try to give if I see the data... (if you are interested, of
course :)

Post by Ron
From owner.table for xml path(mePath), root(meRoot))
Select c.value('contractNumber[1]', 'int') as
ContractNumber, T.c.query('.') From
@schwa.nodes('/Document/ContractDocument') as T(c)

--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/

[Omnibuzz]

Hi Ron,
Thanks for taking your time and writing it..
Well, I think Dan assumed, you wanted to get the information from an XML and
so the CROSS APPLY and node function is required..
But actually speaking, if you are constructing an XML and reading through it
again, then its a performance hit.. You just construct the XML once, you can
get the contractNumber from your select.
What I mean is, the following query will suffice (for the tables that dan
posted).. and that @ is for making it an attribute..

select c.id,a.C
from #contract as c
cross apply (select c.id as "@id",D.Name as "@name", D.Length as "@length"
from #details AS D where D.ID = C.ID
for XML PATH('C')) as a(c)

And for getting the exact result as Dan's query, you write it this way...

select c.id,cast(a.C as xml)
from #contract as c
cross apply (select c.id,
cast((SELECT D.Name as "D/@Name", D.Length as "D/@Length" from #details AS
D where D.ID = C.ID
FOR XML path('')) as xml) for XML PATH('C')) as a(c)

here you don't parse the XML, you just build it once and should .. You can
directly use an insert clause to it..
And maybe if you run those two queries parallely and check the cost relative
to the batch you will see that this takes almost no time. And this might show
a visible difference when there are lots of rows in the table.
I hope I made sense :)

[Omnibuzz]

Hi Ron,
I assume you are using 2005.. and since you didn't give the data, I cannot
give you an example. But, from what I understood, you can do that using the
nodes() function for XML and a cross apply. You will get detailed info here.

http://msdn2.microsoft.com/en-us/library/ms188282.aspx

hope this helps.

[unknown]

Here is a simple example of using the nodes function to shred a contract document:


DECLARE @c xml
SET @c =
'<contract SN="1234">
<detail name="party 1">Joe</detail>
<detail name="party 2">Jane</detail>
<detail name="location">
<state>CA</state>
<city>LA</city>
</detail>
</contract>
'

SELECT T.C.value('../@SN', 'INT') as ContractID,
T.C.query('.')
FROM @c.nodes('contract/detail') AS T(C)


ContractID
----------- -------------------------------------------------------------------
1234 <detail name="party 1">Joe</detail>
1234 <detail name="party 2">Jane</detail>
1234 <detail name="location"><state>CA</state><city>LA</city></detail>



Dan