|
|
|
SSC-Addicted
 
Group: General Forum Members
Last Login: 2 days ago @ 1:12 PM
Points: 414,
Visits: 2,250
|
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Saturday, May 23, 2009 9:16 AM
Points: 21,
Visits: 87
|
|
As I see, the Sort in 'EXPLICIT' query will be incorrect for more then 100 Employees.
I think that there is better another sort:
ORDER BY [Department!1!DepartmentID], Tag, Parent
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, October 20, 2009 10:20 AM
Points: 1,038,
Visits: 271
|
|
Jacob,
Thanks for putting that article out for us to read. I have a question. I am able see the result, but somehow my result are incomplete ie... it seems to be a cut off. Is there a limit as far as how large an xml file could be? I read and see that it is set to a 2mb default file size by the file that I am getting is only 89k... I open it in word to do character count and only get 4-5000 char vs 45,000 + chars that is in my variable. What am I missing?
This is the model that I was using earlier.
DECLARE @x xml
SET @x=(SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML RAW, TYPE)
SELECT @x
Thanks in advance.
Sopheap
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: 2 days ago @ 1:12 PM
Points: 414,
Visits: 2,250
|
|
this must be a limitation on displaying the text at SSMS. refer this article http://www.sqlservercentral.com/articles/XML/62054/ that shows how to retrieve XML results with ADO.NET
Jacob Sebastian, SQL Server MVP
http://beyondrelational.com/blogs/jacob/
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Sunday, July 26, 2009 3:25 AM
Points: 5,
Visits: 63
|
|
sir i have this record
error_reference_uid item_number error_code
1234567 13579 odd
1234567 2468 even
and i want to create this xml.
Errors error_reference_uid="1234567"
Error item_number="13579" error_code="odd"
Error item_number="2468" error_code="even"
Errors[/p]
how can i do that???
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: 2 days ago @ 1:12 PM
Points: 414,
Visits: 2,250
|
|
-- SAMPLE TABLE
DECLARE @t TABLE (
error_reference_uid VARCHAR(10),
item_number VARCHAR(10),
error_code VARCHAR(10) )
-- SAMPLE DATA
INSERT INTO @t (error_reference_uid,item_number,error_code) SELECT '1234567','13579','odd'
INSERT INTO @t (error_reference_uid,item_number,error_code) SELECT '1234567','2468','even'
INSERT INTO @t (error_reference_uid,item_number,error_code) SELECT '1234568','13579','odd'
INSERT INTO @t (error_reference_uid,item_number,error_code) SELECT '1234568','2468','even'
-- QUERY
SELECT
error_reference_uid as '@error_reference_uid',
(
SELECT
item_number AS '@item_number',
error_code AS '@error_code'
FROM @t tc
WHERE tc.error_reference_uid = tp.error_reference_uid
FOR XML PATH('Error'), TYPE
)
FROM @t tp
GROUP BY error_reference_uid
FOR XML PATH('Errors')
/*
OUTPUT:
[Errors error_reference_uid="1234567"]
[Error item_number="13579" error_code="odd" /]
[Error item_number="2468" error_code="even" /]
[/Errors]
[Errors error_reference_uid="1234568"]
[Error item_number="13579" error_code="odd" /]
[Error item_number="2468" error_code="even" /]
[/Errors]
*/
Jacob Sebastian, SQL Server MVP
http://beyondrelational.com/blogs/jacob/
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Sunday, July 26, 2009 3:25 AM
Points: 5,
Visits: 63
|
|
thank you so much.. that's look very easy for you.. to share me solution which is i am you gonna laugh.
i create a table variable and manually create the xml through iterating the records. i already implemented by solution in the production server. but surely i will revised my solution using your statement.
thank you so much
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Thursday, August 21, 2008 6:58 AM
Points: 7,
Visits: 4
|
|
How do you use a UNION ALL with XML PATH?
I have two queries I need to pull together to get create my child nodes for and I am using a UNION ALL but I get the following error:
Error:
The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.
I searched google on this but found no solution.
Below is my SQL code:
-- <Mappings>
'boolean' AS "Mappings/@AutoCleanup",
'boolean' AS "Mappings/@PreserveExistingRecords",
(
SELECT
u.Uses AS "@Name",
'Category' AS "@EntityType",
'uniqueidentifier' AS "@GUID"
FROM
Uses u
INNER JOIN CaseToUses ctu ON u.ID = ctu.UsesID
INNER JOIN Cases c2 ON ctu.CaseID = c2.ID
WHERE
c2.ID = 4671--c.ID
UNION ALL
SELECT
ctc.ComputerMFG AS "@Name",
'Section' AS "@EntityType",
'uniqueidentifier' AS "@GUID"
FROM
CaseToComputer ctc
INNER JOIN Cases c2 ON ctc.CaseID = c2.ID
WHERE
c2.ID = 4671--c.ID
FOR XML PATH('Entity'), TYPE
)
AS "Mappings",
-- </Mappings>
Below is the result I want:
<Mappings AutoCleanup="boolean" PreserveExistingRecords="boolean">
<Entity Name="Military Cases" EntityType="Category" GUID="uniqueidentifier" />
<Entity Name="Footlocker Cases" EntityType="Category" GUID="uniqueidentifier" />
<Entity Name="IBM" EntityType="Section" GUID="uniqueidentifier" />
<Entity Name="Dell" EntityType="Section" GUID="uniqueidentifier" />
</Mappings>
Thank you if you can help!
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: 2 days ago @ 1:12 PM
Points: 414,
Visits: 2,250
|
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Thursday, August 21, 2008 6:58 AM
Points: 7,
Visits: 4
|
|
Here's scripts to create the tables. I am working on the data insert.
/****** Object: Table [dbo].[Cases] Script Date: 08/20/2008 13:29:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Cases](
[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[CaseName] [varchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
/****** Object: Table [dbo].[Uses] Script Date: 08/20/2008 13:29:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Uses](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Uses] [nvarchar](50) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
/****** Object: Table [dbo].[CaseToUses] Script Date: 08/20/2008 13:28:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CaseToUses](
[CaseID] [numeric](18, 0) NOT NULL,
[UsesId] [int] NOT NULL,
[cu_id] [numeric](18, 0) IDENTITY(1,1) NOT NULL
CONSTRAINT [PK_CaseToUses] PRIMARY KEY CLUSTERED
(
[cu_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
/****** Object: Table [dbo].[CaseToComputer] Script Date: 08/20/2008 13:28:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CaseToComputer](
[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[CaseID] [numeric](18, 0) NULL,
[ComputerMFG] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
|
|
|
|
