August 13, 2012 at 8:06 am
I use a nested query to concatenate row data for an aggregate. The syntax I use within the nested query is
for XML PATH ('')
Can someone explain what is supposed to go between the quotes above? I did some tests and it looks like it just encloses my row data in start and ending HTML like tags.
August 13, 2012 at 8:37 am
The name of the root node goes there.
Try this:
SELECT *
FROM (VALUES (1),(2)) AS V(Col)
FOR XML PATH('RootNote'), TYPE;
Result:
<RootNode>
<Col>1</Col>
</RootNode>
<RootNode>
<Col>2</Col>
</RootNode>
Change the string, change the name of the node.
A null-string (empty string, zero-length string) there leaves off the root node.
SELECT *
FROM (VALUES (1),(2)) AS V(Col)
FOR XML PATH(''), TYPE
Result:
<Col>1</Col>
<Col>2</Col>
The null-string root-name is useful in using For XML to concatenate column values together.
SELECT ',' + Col
FROM (VALUES ('1'),('2')) AS V(Col)
FOR XML PATH(''), TYPE
Result:
,1,2
From that string, you can use Stuff() to get rid of the leading delimiter (a comma in this case), and have a concatenated, delimited list of values. Very useful in certain cases. The string math in the query means the values have no name, and the null-string root-name means the rows have no name, so the For XML ends up being tagless.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 13, 2012 at 8:38 am
if you were actually building xml strings, the value inside would be a tagname for the XML.
SELECT
name
FROM sys.columns sc
FOR XML PATH('ParentTag')
One of the neat things about the FOR XML tag is how we can use it to concatenate rows into a single string...that's usually where you see the FOR XML PATH('') with teh empty string value.
SELECT DISTINCT
t.name,
sq.Columns
FROM sys.tables t
JOIN (
SELECT OBJECT_ID,
Columns = STUFF((SELECT ',' + name
FROM sys.columns sc
WHERE sc.object_id = s.object_id
FOR XML PATH('')),1,1,'')
FROM sys.columns s
) sq ON t.object_id = sq.object_id
Lowell
August 13, 2012 at 8:40 am
I just noticed, I kept calling it the "root node", which isn't fully accurate. It's actually the row-level node. The root node would be for the whole dataset.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 13, 2012 at 8:58 am
Lowell, what exactly is a "tagname" in XML?
August 13, 2012 at 9:02 am
guerillaunit (8/13/2012)
Lowell, what exactly is a "tagname" in XML?
just like html, a tag is just a descriptor that describes or names it's contents.
only in a specific context might it have any special meaning.
for html browsers, certain tags (html, body, div, table, td, etc) have special meanings.
in raw xml, they are just tags; but an application you use or build might process that xml by parsing and using specific tags.
Lowell
August 14, 2012 at 4:39 pm
Another way to create a delimited string from table data WITHOUT using XML. This can easily be turned into a function if so desired. If so, it's best used as a Table-Valued Function and used with a CROSS APPLY.
DECLARE
@strTest NVARCHAR(MAX)
,@strDelimiter NVARCHAR(10)
SET @strDelimiter = '|'
SELECT TOP (5)
@strTest = COALESCE(@strTest + @strDelimiter,'')
+ CAST(ColName AS NVARCHAR(MAX))
FROM
dbo.SomeTable
SELECT
@strTest
August 14, 2012 at 5:05 pm
But to answer your question, sometimes XML PATH('') is left blank and other times you can specify a node value as in this example. Notice in the rendered XML how you can create simple nodes such as "Given Name" or nodes with Types+Data as in "Postal Address" or multiple nodes with the same name such as "Telephone Number".
SELECT
(
SELECT
'subject' AS 'PersonName/@type'
,ISNULL('JOHN','') AS 'PersonName/GivenName'
,ISNULL('A','') AS 'PersonName/MiddleName'
,ISNULL('DOE','') AS 'PersonName/FamilyName'
FOR
XML PATH(''),TYPE
)
,(
SELECT
'current' AS 'PostalAddress/@type'
,ISNULL(REPLACE(CONVERT(VARCHAR(10),'3/22/2012',111),'/','-'),'') AS 'PostalAddress/@validFrom'
,ISNULL(CAST('23456' AS VARCHAR(50)),'') AS 'PostalAddress/PostalCode'
,ISNULL(CAST('MO' AS VARCHAR(50)),'') AS 'PostalAddress/Region'
,ISNULL(CAST('ST LOUIS' AS VARCHAR(50)),'') AS 'PostalAddress/Municipality'
,ISNULL(CAST('123 MAIN ST' AS VARCHAR(50)),'') AS 'PostalAddress/DeliveryAddress/AddressLine'
,ISNULL(CAST('APT 5' AS VARCHAR(50)),'') AS 'PostalAddress/DeliveryAddress/Unit'
FOR
XML PATH(''),TYPE
)
,(
SELECT
(
SELECT
'home' AS 'Telephone/@Name'
,ISNULL(CAST('555-678-1234' AS VARCHAR(50)),'') AS 'Telephone/Number'
FOR
XML PATH(''),TYPE
)
,(
SELECT
'mobile' AS 'Telephone/@Name'
,ISNULL(CAST('555-678-1234' AS VARCHAR(50)),
'') AS 'Telephone/Number'
FOR
XML PATH(''),TYPE
)
,(
SELECT
'work' AS 'Telephone/@Name'
,ISNULL(CAST('555-678-1234' AS VARCHAR(50)),'') AS 'Telephone/Number'
FOR
XML PATH(''),TYPE
)
,ISNULL('test@aol.com','') AS 'InternetEmailAddress'
FOR
XML PATH('ContactMethod'),TYPE
)
FOR XML PATH('PersonalData'), TYPE
The XML generated by the above:
<PersonalData>
<PersonName type="subject">
<GivenName>JOHN</GivenName>
<MiddleName>A</MiddleName>
<FamilyName>DOE</FamilyName>
</PersonName>
<PostalAddress type="current" validFrom="3-22-2012">
<PostalCode>23456</PostalCode>
<Region>MO</Region>
<Municipality>ST LOUIS</Municipality>
<DeliveryAddress>
<AddressLine>123 MAIN ST</AddressLine>
<Unit>APT 5</Unit>
</DeliveryAddress>
</PostalAddress>
<ContactMethod>
<Telephone Name="home">
<Number>555-678-1234</Number>
</Telephone>
<Telephone Name="mobile">
<Number>555-678-1234</Number>
</Telephone>
<Telephone Name="work">
<Number>555-678-1234</Number>
</Telephone>
<InternetEmailAddress>test@aol.com</InternetEmailAddress>
</ContactMethod>
</PersonalData>
:
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply