forxml

SQL 2005 For XML Explicit - Need help formatting

I have a table with a structure like the following: ------------------------------ LocationID | AccountNumber ------------------------------ long-guid-here | 12345 long-guid-here | 54321 To pass into another stored procedure, I need the XML to look like this: <root><clientID>12345</clientID><clientID>54321</clientID></root> The...

SQL Server FOR XML adding in a tag

Heya, So I am pulling data from a SQL Server 2000 DB then converting it to XML using FOR XML AUTO. The XML I get back looks like this. <Order OrderNumber="2000004" DeliveryPickupCharge="5.9900" SalesTaxTotal="0.0000" SubTotal="0.0000" Total="5.9900"> <Customer FirstName="Anthony" LastName="Caporale"> <Product ProductName="Pap...

FOR XML PATH in Sql Server

NOTE: I have solved the majority of this problem but have run into a snag. Read to the bottom please. You will see where I added a (NOTE) section. TIA. I have a rather extensive join query that I want dumped to XML. I have it almost working but I am missing a concept in here somewhere. My query (abbreviated) looks like: SELECT C...

Sql Server - Capture an XML query and save it to a table?

I would like to run a (extensive) query that produces one line of XML. This XML represents about 12 tables worth of relational data. When I "delete" a row from the top level table I would like to "capture" the state of the data at that moment (in XML), save it to an archive table, then delete all the child table data and finally mark t...

return parent and child data using FOR XML in a stored proc

If I have a parent table and a child table where there can be one parent to many children, how can I go about returning the following xml from a stored procedure in a stored procedure? <Parents> <Parent> <ID>Integer</ID> <Children> <Child> <ID>Integer</ID> <Text>String</Text> ...

t-sql most efficient row to column? crosstab for xml path, pivot

I am looking for the most performant way to turn rows into columns. I have a requirement to output the contents of the db (not actual schema below, but concept is similar) in both fixed width and delimited formats. The below FOR XML PATH query gives me the result I want, but when dealing with anything other than small amounts of data, ...

bcp queryout "for xml path" delimiters

Hello, I am trying to export to xml file using bcp command with queryout and select... FOR XML PATH option. I have a test data of 5000 records. Expected output is: <row> <column1><\column1> <column2><\column2> <\row> *line break here* <row> <column1><\column1> <column2><\column2> <\row> *line break here* However, bcp doe...