March 19, 2008 at 12:05 am
Comments posted to this topic are about the item XML Workshop XVI - Shaping the XML results
.
March 19, 2008 at 11:07 am
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
March 24, 2008 at 2:04 pm
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
March 24, 2008 at 11:54 pm
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
.
April 4, 2008 at 12:18 pm
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???
April 5, 2008 at 1:06 am
-- 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]
*/
.
April 5, 2008 at 7:49 am
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
August 20, 2008 at 11:12 am
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!
August 20, 2008 at 11:25 am
could you post the script to create the tables and some test data?
.
August 20, 2008 at 11:33 am
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
August 20, 2008 at 11:45 am
I hope this is correct...here is the data:
INSERT INTO Cases (CaseName)VALUES('My Case')
INSERT INTO Uses (Uses)VALUES('Military Cases')
INSERT INTO Uses (Uses)VALUES('Footlocker Cases')
INSERT INTO CaseToUses (CaseID, UsesID)VALUES(1,1)
INSERT INTO CaseToUses (CaseID, UsesID)VALUES(1,2)
INSERT INTO CaseToComputer (CaseID, ComputerMFG)VALUES(1,'IBM')
INSERT INTO CaseToComputer (CaseID, ComputerMFG)VALUES(1,'Dell')
August 20, 2008 at 11:50 am
This should be the complete query:
SELECT
c.ID,
c.CaseName,
-- <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 = 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 = c.ID*/
FOR XML PATH('Entity'), TYPE
)
AS "Mappings"
-- </Mappings>
FROM
Cases c
WHERE
c.CaseName <> ''
ORDER BY
c.ID
FOR XML PATH('Product'), ROOT('MyXML')
August 20, 2008 at 12:32 pm
Does this give you the required output?
SELECT
c.ID,
c.CaseName,
'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 = c.ID
FOR XML PATH('Entity'), TYPE
) AS Mappings,
(
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 = c.ID
FOR XML PATH('Entity'), TYPE
) AS Mappings
FROM
Cases c
WHERE
c.CaseName <> ''
ORDER BY
c.ID
FOR XML PATH('Product'), ROOT('MyXML')
.
August 20, 2008 at 12:36 pm
Pls replace the smily icon with a ")"
.
August 20, 2008 at 12:47 pm
THANK YOU!! Works great! So instead of using a 'UNION ALL', just create two separate 'SELECTS' with the same 'AS'. Awesome, thanks again!
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply