Now I'm totally lost....

Why don't you use one or any combination of the tags within the OrderDocumentRequest element, like caseId(2923029), partitionCode (ADK), BatchNumber (3012578260) or tag(TadID)?

It's really hard to guess...

something like SELECT newid() AS ID,

Breq.value('caseId[1]','varchar(200)') AS [caseID],

Doc.value('Name[1]', 'varchar(200)') AS [Name],

Doc.value('Value[1]', 'varchar(max)') AS [Value]

FROM @xml.nodes('/OrderDocumentBatch/OrderDocumentRequest') AS B(Breq)

...

triple post.

Further discussion here.

triple post.

Further discussion here.

duplicate post.

please continue discussion here .

Now it gets confusing... :ermm:

Your sample data do contain the tag [caseID], which can be used to differentiate between each [orderdocumentrequest].

If that's not the case, you still could use all...

Try the following code. It's based on your sample with two minor changes:

First, I made it case sensitive to match your sample data and secondly, I added the caseID, assuming...

Just a few thoughts on the issue:

1) Like Barry advised, you should think about the process and the purpose of the data again. What data are part of the scan...

The first question that comes into my mind when reading the requirement:

What is the business requirement of adding a NEWID to an xml variable using SQL server and leave it...

Please search for "PIVOT" or "CROSS TAB" on this site.

You'll find a number of posts where at least one should answer your question.

You could also go directly to one of...

A few more things to add:

#1) I forgot to thank you for the way you've provided sample data and that you took the time to dive into the solution I...

kramaswamy (8/17/2009)

---

So, let me see if I understand this right.

What you're essentially doing is, getting a total of the subscribers on a monthly basis, and then, using the results stored...

I assume you have something like a calendar table...

If not, I've included a calendar (CTE_calendar) in the code below.

Also, please note that I've used a temp table with a PK...

Please take the time to help us help you.

In order to get a solution to the problem you described some of us like to have ready to use data (including...

Basically, there are two ways for this common requirement:

one is the PIVOT function (see BOL = BooksOnLine, the SQL help system, for details) and the other option uses the...