Check function CONVERT in BOL.

SELECT CONVERT(char(50), CAST(113 AS binary(4)), 1) AS c1;

GO

Just keep in mind that the xml methods are case sensitive.

DECLARE @x xml = '<event name="login" package="sqlserver" timestamp="2014-02-19T23:53:54.299Z">

<data name="is_cached">

<value>true</value>

</data>

<data name="is_dac">

...

Yes, there are other options like using a cursor or loop but the FOR XML PATH one is simple and performs well.

We do not have a string concatenation ordered set...

To add to Sean's response, see if you can get a system views map for whatever version you are using. There is a lot you can learn from there mainly...

Have you checked the catalog views sys.tables and sys.columns?

Here is an example without taking into consideration user types.

SELECT

t.name AS tn,

sc.*

FROM

sys.tables AS t

INNER JOIN

sys.columns AS sc

ON sc.object_id = t.object_id

INNER JOIN

sys.types AS...

Since you already has a hierarchyid type column in your sample, may be using methods from this type will do it for you.

SELECT

A.Level.ToString() AS MPath,

A.*,

A.Level.GetLevel() as [Level],

ISNULL(C.sum_A, A.A) AS TotalA,

ISNULL(C.sum_B,...

Tony,

Thanks for providing the sample data. It makes our life easier. 🙂

Here is the example using the transitive closure but as I said this is not the best solution...

Tony,

Can you provide some DDL (table definition and insert statements)?

One way to accomplish this is traversing one level at a time in a procedural way. You can start from the...

Thanks, Luis!

Didn't notice the OP posted the exact error but I had an idea what the error could be. I was just expalining why he/she is getting the error.

Another solution...

Columns in the SELECT clause that are not part of the GROUP BY should participate in an aggregation function.

ph.CalculatedTotalTime,

ph.MachGrpCode,

ph.EmpId,

pd.PartCode

Can you post the exact error msg you are getting?

I could be wrong for simplifying it that much but I think we could do this in one pull from [TripData] using the idea from Sean.

select

*,...

Try:

SET NOCOUNT ON;

USE tempdb;

GO

DECLARE @T TABLE (

sk int NOT NULL IDENTITY(1, 1) PRIMARY KEY,

x xml NOT NULL

);

INSERT INTO @T (x)

VALUES

('<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">

<Relationship Id="rId13" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="../media/image357.png" />

<Relationship Id="rId18" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink" Target="PDF''s/BB-7110.PDF" TargetMode="External" />

<Relationship...

Previous suggestions using two foreign key constraints will do it even though it is a not common design.

In your sample document there was no "ID" for purchase orders so I added to make it easier.

What elements are you referencing for "ParentID"?

PO (50)?

Items (20, 21)?

Payments (30, 31)?

ParentId (50,...

Can you post the expected result?

It is not clear if there is a relashionship between ites and payments.

SET NOCOUNT ON;

USE tempdb;

GO

DECLARE @x xml = N'<PurchaseOrder ID="1" BuyerName="Car Corporation" Date="1 Jan...