Post reply

XML Format

  • May 21, 2015 at 5:02 am

    #317617

    I'm just doing practice questions for MCSA and came across this:

    Q: Return query in this format:

    <Invoices InvoiceID="1001" Date="1212-10-01T00:00:00", Value ="1000.00" Customer="CustomerName" ShippedTo="Customer City" />

    A:

    SELECT in.InvoiceID, in.InvoiceDate AS [DATE], in.InvoiceValue AS [Value], cu.CustomerName AS [Name], cu.CustomerCity AS [ShippedTo] FROM Invoices AS in

    INNER JOIN Customers AS cu ON in.CustomerID = cu.CustomerID = 1001

    FOR XML RAW

    I can't see how this can be right? Surely it should start <row ?????

  • May 21, 2015 at 6:16 am

    #1799624

    This might help you:

    https://msdn.microsoft.com/library/bb500156.aspx

    The following query includes columns of xml type. The query retrieves product model ID, name, and manufacturing steps at the first location from the Instructions column of the xml type.

    Example

    USE AdventureWorks2012;

    GO

    SELECT ProductModelID, Name,

    Instructions.query('

    declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"

    /MI:root/MI:Location[1]/MI:step

    ')

    FROM Production.ProductModel

    FOR XML RAW ('ProductModelData')

    GO

    The following is the result. Note that the table stores manufacturing instructions for only some product models. The manufacturing steps are returned as subelements of the <ProductModelData> element in the result.

    <ProductModelData ProductModelID="5" Name="HL Mountain Frame" />

    <ProductModelData ProductModelID="6" Name="HL Road Frame" />

    <ProductModelData ProductModelID="7" Name="HL Touring Frame">

    <MI:step> ... </MI:step>

    <MI:step> ... </MI:step>

    </ProductModelData>

    If the query specifies a column name for the XML returned by the XQuery, as specified in the following SELECT statement, the manufacturing steps are wrapped in the element that has the specified name.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply